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