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




IDENTITY (data_type [ , seed , increment ] ) AS column_name

Arguments
data_type
Any types of the integer data type  except for the bit data type or decimal data type
seed
Is the integer value to be assigned to the first row in the table. Each subsequent row is assigned the incremented value
increment
Is the integer value to add to the seed value for successive rows in the table.

Default value for seed and increment is 1

SELECT *

FROM   sales.Store

We will insert this data in new table along with Identity column

SELECT IDENTITY(int, 1, 1) AS temp_id,  *

INTO   sales.temp_store

FROM   sales.store

SELECT *

FROM   SALES.temp_store