IDENTITY (Function) (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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.
To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.
Applies to: SQL Server (SQL Server 2008 through current version).
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;
The following example inserts all rows from the Contact table from the AdventureWorks2012database 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