Computed Columns 2

Sometimes we are using some scalar function to generate computed column values

This values also not persisted until the function is marked as schema bound

Lets see example here

Computed columns 1

computed columns

use test

We used computed columns many times , but we can get most benefit of computed columns when we can mark them

as persisted.

For this columns must be deterministic.

Lets see how can we get it

How get physical location of row.

Its undocumented command and gives physical location of the row.

This helps while doing advanced trouble shooting.

Its fun to get internals of MSSQL Server doing regular tasks.

First we will look at %%physloc%%

Table Valued Parameters

Table Valued parameters are user defined table types and used to pass multiple rows  of data  as input to procedure or function.  

Benefits 

* we dont need many parameters

* we dont need temporary tables

* We dont need XML to pass multiple values

* pass multiple values of different data types

* Enable client to specify sort order and unique keys

* SQL 2012 onwards table valued parameters are cached for all queries.

SQL Variant

SQL Variant

 Its data type that stores values of various SQL Server-supported data types.

 In certain conditions where we are not sure about datatype we can define it as sql_variant for testing purpose.

 Its supporting int,char and binary value but will not support char(max)/varchar(max)/nvarchar(max) .

sp_sequence_get_range

This proc assigns range of sequence values  to current application along with metadata.

Functions to get identity value

There are 3 functions to get current identity value

@@identity

Returns last identity value generated by last statement  in current session for all scopes

If statement fires trigger and identity value generated by that trigger than @@identity

will give value generated by that trigger

How to use sequence

What is sequence , we saw it in previous article

Now we will use it practically to understand how it works

First we will create a sequence

Local Variables

Local Variables

I would like to point one interesting property how values are assigned to local variables

SELECT *

FROM   sales.store

WHERE  SalesPersonID = 99999999

DBCC CHECKDENT

DBCC CHECKDENT

 

Gives info about current identity value , also we can reset identity value to new value.

IDENTITY Function

IDENTITY Function 

Its introduced in SQL Server 2008 , used with INTO clause to insert identity value in the table .
Its used only with SELECT INTO clause can not used with any other statement

SET IDENTITY_INSERT ON

We have created a sample table in previous article of Identity column

Now we will look how to insert values explicitly in this tables

*All column names must be specified in insert statement

* set identity_insert off after completing insert

Identity property

Identity

We can define a column with identity property in a table.

Its auto increment number and has two arguments

USE OF XACT_ABORT

With use of XACT_ABORT we can manage either entire transaction will rollback or
only statement which raises error

When XACT_ABORT is ON entire transaction will rollback in case of run time error

Running sum simplified in SQL 2012

Running sum is a regular requirement in query for developer and many times developer has to run number  of complex  queries or self joins to get running sum
But now since SQL  2012 its very easy and funny and simplified.

Filtered index

Filtered index

Filtered index is a feature added in SQL Server 2008 and serves a great purpose for some of the requirements
As a general we all know that index improves search performance if INDEX is created properly
But some cases where we font requires data from entire column but only some filtered values
Even for this we have to create index for data entire column.
Whenever we query this column it has to read index for all values  column.

How to avoid MSDTC in linked query

As we all are aware of linked servers and how to use it
We can use it to query remote server as well as copy data from one server to other server
Till now if we want to execute procedure on remote server and insert data on to local server using linked server we need MSDTC enabled on the server

HashBytes

HashBytes

Sometimes people have confusion about use of hash or encryption
There are 2 basic difference between them on which you can decide how to use it

IndexProperty

Recently I had problem of slow insert on tables
During optimization we changed FillFactor of indexes and that improved the performance of insert.
We change the fill factor to 50%.

sys.dm_exec_procedure_stats DMV to get cached proc stats

We all are used to sys.dm_exec_query_stats which gives us useful information for queries running on the server.
Similarly in SQL 2008 we can get details for procedures. For this we will have to use sys.dm_exec_procedure_stats
It is supported in SQL 2008 and higher versions.