Identity function tutorial in SQL Server

This article explores the Identity function in SQL Server with examples and differences between these functions.

Overview of IDENTITY columns

In SQL Server, we create an identity column to auto-generate incremental values. It generates values based on predefined seed (Initial value) and step (increment) value. For example, suppose we have an Employee table and we want to generate EmployeeID automatically. We have a starting employee ID 100 and further want to increment each new EmpID by one. In this case, we need to define the following values.

Seed: 100
Step: 1

USE SQLSHACKDEMO;

CREATE TABLE EmployeeData

([id] [TINYINT] IDENTITY(100, 1) PRIMARY KEY NOT NULL,

[Name] [NVARCHAR](20) NULL

)

ON [PRIMARY];

Let’s insert a few records in this table and view the records.

You can see the first employee gets an ID value 100 and each new records ID value gets an increment of one.

We have a few useful Identity functions in SQL Server to work with the IDENTITY columns in a table. Let’s explore the following IDENTITY functions with examples.

SQL @@IDENTITY Function
SQL SCOPE_IDENTITY() Function
SQL IDENT_CURRENT Function
SQL IDENTITY Function

SQL @@IDENTITY Function

We use system function @@IDENTITY to return the maximum used IDENTITY value in a table for the IDENTITY column under the current session. Once we insert a row in a table, the @@IDENTITY function column gives the IDENTITY value generated by the statement. If we run any query that did not generate IDENTITY values, we get NULL value in the output. The SQL @@IDENTITY runs under the scope of the current session. We cannot use it on a remote or linked server.

Let’s understand it with the following example.

Step 1: We have a current maximum identity value 110 in EmployeeData table
Step 2: In the current session, we insert a record in the EmployeeData table. It increments the identity value by one
Step 3: We can verify that the maximum used identity value is 111
Step4: We use the SELECT @@IDENTITY function in the current session to get the identity value generated in this session

Let’s insert multiple records in this table and execute the SELECT @@IDENTITY.

If we insert multiple records in a session, SELECT @@Identity returns the maximum IDENTITY value generated in this session. In the following example, we insert three records, and it increments IDENTITY values by three from the current identity value 111.

If the statement did not generate any identity values, it returns NULL values in the output.

SQL SCOPE_IDENTITY() function

We use SCOPE_IDENTITY() function to return the last IDENTITY value in a table under the current scope. A scope can be a module, trigger, function or a stored procedure. We can consider SQL SCOPE_IDENTITY() function similar to the @@IDENTITY function, but it is limited to a specific scope. It returns the NULL value if this function is involved before an insert statement generates value under the same scope.

In the following example, we see that both the @@IDENTITY and SCOPE_IDENTITY() return the same value in the current session and similar scope.

Let’s understand the difference between SCOPE_IDENTITY() and @@IDENTITY with another example.

Let’s consider we have two tables EmployeeData and Departments table. We create an INSERT trigger on the EmployeeData table. Once we insert any row on EmployeeData, it calls to defined trigger for inserting a row in Departments.

Drop table EmployeeData

Drop table Departments

CREATE TABLE EmployeeData

([id] [TINYINT] IDENTITY(100, 1) PRIMARY KEY NOT NULL,

[Name] [NVARCHAR](20) NULL

)

CREATE TABLE Departments

(DepartmentID INT IDENTITY(100, 5) PRIMARY KEY,

Departmentname VARCHAR(20) NULL

);

In the following query, we create a trigger to insert default value ‘IT’ in the departments table for every insert in the EmployeeData table.

CREATE TRIGGER T_INSERT_DEPARTMENT

ON EmployeeData

FOR INSERT AS

BEGIN

INSERT Departments VALUES ('IT')

END;

Let’s insert value in the Employee data table and view the output of both @@IDENTITY and SCOPE_IDENTITY() functions.

In the current session, we inserted data into the EmployeeData table. It generates an identity in this table. The identity seed value is 1 for the EmployeeData table.

Once we insert value in the EmployeeData table, it fires a trigger to insert value in the Departments table. The identity seed value is 100 for the Departments table.

We get the output 100 for the SELECT @@IDENTITY function
SCOPE_IDENTITY function returns identity value under the current scope only. It gives output 1 for this function

SQL IDENT_CURRENT() function

We use the IDENT_CURRENT function to return the last IDENTITY value generated for a specified table under any connection. It does not consider the scope of the SQL query that generates identity value. We need to specify the table for which we want to check the identity value.

In the following screenshot, we can see that we insert data in Session id 64 and it generates identity value 2 in the EmployeeData table.

We can check in another connection window about the current identity value for the EmployeeData table and get the same output as identity value 2.

1	SELECT IDENT_CURRENT('EmployeeData') AS IdentityValue

SQL IDENTITY Function

In my previous article, we explored SQL SELECT INTO Statement, to create a new table and inserted data into it from the existing table. We can use the SQL IDENTITY function to insert identity values in the table created by SQL SELECT INTO statement.

By default, if a source table contains an IDENTITY column, then the table created using a SELECT INTO statement inherits it. Consider a scenario in which you want to create a table using the SELECT INTO statement from the output of a view or join from multiple tables. In this case, you want to create an IDENTITY column in a new table as well.

Note: SQL IDENTITY function is different from the IDENTITY property we use while creating any table.

We need to specify a data type for a column to use SQL Identity function. We also need to specify SEED and Step values to define an identity configuration.

We cannot use SQL IDENTITY Function in a Select statement. We get the following error message.

Msg 177, Level 15, State 1, Line 2 The IDENTITY function can only be used when the SELECT statement has an INTO clause.

Let’s create a table using the SQL SELECT INTO statement with the following query.

SELECT TOP (10) IDENTITY( INT, 100, 2) AS NEW_ID,

[PersonType],

[NameStyle],

[Title],

[FirstName],

[MiddleName],

[LastName],

[Suffix]

INTO TEMPTABLE

FROM [AdventureWorks2017].[Person].[Person];

Once the statement executes, check the table properties using sp_help command.

1	sp_help 'TEMPTABLE'

You can see the IDENTITY column in the TEMPTABLE properties as per the specified conditions.

Let’s look at another example. Execute the following command.

SELECT IDENTITY( INT, 100, 2) AS NEW_ID,

ID,

Name

INTO temp2

FROM employeedata;

We already have an IDENTITY column in the EmployeeData table. The new table temp2 also inherits the IDENTITY column. We cannot have multiple IDENTITY columns in a table. Due to this, we get the following error message.

Msg 8108, Level 16, State 1, Line 1 Cannot add identity column, using the SELECT INTO statement, to table ‘temp2’, which already has column ‘ID’ that inherits the identity property.

Summary

SQL IDENTITY Functions SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY returns similar output for an IDENTITY columns in current session
SCOPE_IDENTITY returns values under the current scope only. We can define scope as a module, Stored procedure, trigger or a function
IDENT_CURRENT function returns identity value for a specified table regarding a connection that modified the value
We can use the SQL IDENTITY function to define IDENTITY in a table created using SQL SELECT INTO statement

Conclusion

had In this article, we explored the Identity functions in SQL Server. We should be familiar with the functions to use them in writing t-SQL queries. If you have comments or questions, feel free to leave them in the comments below.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023