IDENTITY (Function) (Transact-SQL)

data_type

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.

seed

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.

increment

Is the integer value 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.

Returns the same as data_type.

Because this function creates a column in a table, a name for the column must be specified in the select list in one of the following ways:

--(1)
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable;

--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable;

--(1)
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable;

--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable;

The following example inserts all rows from the Contact table from the AdventureWorks 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.

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Person.NewContact', N'U') IS NOT NULL
    DROP TABLE Person.NewContact;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;
GO
SELECT  IDENTITY(smallint, 100, 1) AS ContactNum,
        FirstName AS First,
        LastName AS Last
INTO Person.NewContact
FROM Person.Person;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO
SELECT ContactNum, First, Last FROM Person.NewContact;
GO

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Person.NewContact', N'U') IS NOT NULL
    DROP TABLE Person.NewContact;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;
GO
SELECT  IDENTITY(smallint, 100, 1) AS ContactNum,
        FirstName AS First,
        LastName AS Last
INTO Person.NewContact
FROM Person.Person;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO
SELECT ContactNum, First, Last FROM Person.NewContact;
GO

Reference