Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.
Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category, except for the bit data type, or decimal data type.
Is the integer 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. If neither seed nor increment is specified, both default to 1.
Is the integer value to add to the seed value for successive rows in the table.
Is the name of the column that is to be inserted into the new table.
The following example inserts all rows from the Person table from the AdventureWorks2008R2 database into a new table called NewContact. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the NewContact table.
IF OBJECT_ID (N'Person.NewContact', N'U') IS NOT NULL
DROP TABLE Person.NewContact;
ALTER DATABASE AdventureWorks2008R2 SET RECOVERY BULK_LOGGED;
SELECT IDENTITY(smallint, 100, 1) AS ContactNum,
FirstName AS First,
LastName AS Last
ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL;
SELECT ContactNum, First, Last FROM Person.NewContact;