IDENTITY() function is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.
This function creates a column in a table, a name for the column must be specified in the select list.
Syntax of IDENTITY() Function :
IDENTITY (data_type [ ,seed ,increment ] ) AS column_name
data_type is a data type of an identity column.
seed is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value.
increment t
s the increment to add to the seed value for successive rows in the table.
column_name is the name of the column that is to be inserted into the new table.
Return type of IDENTITY() function is same as data_type.
Examples of IDENTITY() Function :
Example 1 : Use of IDENTITY() function in select clause
SELECT IDENTITY(smallint, 1, 1) AS RegionID, RegionDescription
INTO Region
FROM Regions
SELECT * FROM Region
Output
RegionID RegionDescription
1 Eastern
2 Western
3 Northern
4 Southern
Above example inserts all rows from the Regions table into a new table called Region. The IDENTITY function is used to start identification numbers at 1.