Edit

IDENTITY (Function) (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

Use IDENTITY only in a SELECT statement with an INTO table clause to add an identity column to a new table. Although similar, the IDENTITY function isn't the same as the IDENTITY property that you use with CREATE TABLE and ALTER TABLE.

Note

To create an automatically incrementing number that you can use in multiple tables or call from applications without referencing any table, see Sequence Numbers.

Transact-SQL syntax conventions

Syntax

Syntax for SQL Server, Azure SQL Database, Azure SQL Managed Instance, SQL database in Fabric:

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

Syntax for Fabric Data Warehouse:

IDENTITY ( data_type ) AS column_name

Arguments

data_type

The data type of the identity column. Valid data types for an identity column are any data types in the integer data type category, except for the bit data type, or the decimal data type.

seed

The integer value to assign to the first row in the table. Each subsequent row gets the next identity value, which is the last IDENTITY value plus the increment value. If you don't specify seed or increment, both default to 1.

increment

The integer value to add to the seed value for successive rows in the table.

column_name

The name of the column to insert into the new table.

Return types

Returns the same as data_type.

Remarks

Because this function creates a column in a table, you must specify a name for the column 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;

Support in Microsoft Fabric Data Warehouse

In Fabric Data Warehouse, you can't specify seed or increment values because the system automatically manages these values to provide unique integers. For a column definition in a CREATE TABLE statement, you only need to use BIGINT IDENTITY. For more information, see CREATE TABLE (Transact-SQL) IDENTITY (Property) and IDENTITY in Fabric Data Warehouse.

Examples

The following example inserts all rows from the Contact table in the AdventureWorks2025 database into a new table named NewContact. The IDENTITY function sets the identification numbers to start at 100 instead of 1 in the NewContact table.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID (N'Person.NewContact', N'U') IS NOT NULL  
    DROP TABLE Person.NewContact;  
GO  
ALTER DATABASE AdventureWorks2022 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 AdventureWorks2022 SET RECOVERY FULL;  
GO  
SELECT ContactNum, First, Last FROM Person.NewContact;  
GO

Related content

  • Last updated on