Problem
A SQL Server IDENTITY column is a special type of column that is used to automatically generate key values based on a provided seed (starting point) and increment. SQL Server provides us with a number of functions that work with the IDENTITY column. In this tip, we will go through these functions with examples.
Solution
Due to the importance and the wide usage of the IDENTITY column, there are a number of system functions that help us work with the IDENTITY column. The most common functions are @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT and IDENTITY. While the @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT functions return the values that are inserted into the table’s identity column, there are a few differences between these functions, which we will discuss in this tip.
SQL Server @@IDENTITY Function
The @@IDENTITY is a system function that returns the last IDENTITY value generated for any table with an identity column under the current session, regardless of the scope of the T-SQL statement that generated the value. In other words, if you have multiple rows generated using different INSERT, SELECT INTO or BULK COPY operations, the @@IDENTITY function will return the last identity value generated at the end of the last operation. If these statements didn’t affect any table with an identity column, the @@IDENTITY function will return a NULL value.
SQL Server SCOPE_IDENTITY() Function
The SCOPE_IDENTITY() function returns the last IDENTITY value that is generated for any table with identity column under the current connection, explicitly by the statements running in the current scope. The scope can be a stored procedure, a function, a trigger or a batch of queries. This means that the SCOPE_IDENTITY() function works same as the @@IDENTITY, but limited to a specific scope. If no insert statement is performed on a table with an identity column before calling the SCOPE_IDENTITY() function, it will return a NULL value.
SQL Server IDENT_CURRENT(‘tablename’) Function
The IDENT_CURRENT function returns the last IDENTITY value generated for a specific table under any connection, regardless of the scope of the T-SQL statement that generated that value. In other words, the IDENT_CURRENT function is limited only to a specific table, without being affected by the connection or the scope.
As a summary for what we discussed previously, both the SCOPE_IDENTITY and @@IDENTITY functions will return the last identity values generated in any table in the current session. The SCOPE_IDENTITY function will return the values that are inserted in the current scope only. The @@IDENTITY function is not limited to a specific scope. For the IDENT_CURRENT, you should specify the table that you need to check for its last identity value.
SQL Server Identity Examples
Let’s check the differences of these functions. We will create two new tables with IDENTITY columns, the first one with seed equal to 1, and the second table with seed equal to 10. Both tables have increment equal to 1.
The tables can be created using the T-SQL script below:
USE MSSQLTipsDemo
GO
CREATE TABLE ID_Test
(
ID INT IDENTITY (1,1) PRIMARY KEY,
EmpName nvarchar(50)
)
CREATE TABLE SecondID_Test
(
ID INT IDENTITY (10,1) PRIMARY KEY,
CourName nvarchar(50)
)
The tables are created and now we can check the current identity value for the two tables using the IDENT_CURRENT function by providing the table name as shown below:
SELECT IDENT_CURRENT('ID_Test') AS ID_TestCurrentID
GO
SELECT IDENT_CURRENT('SecondID_Test') AS SecondID_TestCurrentID
GO
The result will show us that the current identity value for each table is equal to the provided seed value, as no record has been inserted yet:
We will insert four records to the first table and three records to the second table using the INSERT statements below:
INSERT INTO ID_Test (EmpName) VALUES ('ALI'),('Mohd'),('Jack'),('John')
INSERT INTO SecondID_Test (CourName) VALUES ('Faisal'),('Zaki'),('Santa')
Let’s check the current identity values again after the insertion process using the IDENT_CURRENT function:
SELECT IDENT_CURRENT('ID_Test') AS ID_TestCurrentID
GO
SELECT IDENT_CURRENT('SecondID_Test') AS SecondID_TestCurrentID
GO
As expected, the identity seed for the first table will be increased by four and the second table by three. The IDENT_CURRENT function result will show the current identity for each table as shown below:
If we check the result of the @@IDENTITY function after the insertion process:
SELECT @@IDENTITY AS IDNT
The function will return the last identity value generated from the two insert statements, which is equal to 12 from the second table:
The SCOPE_IDENTITY function result will be similar to the @@IDENTITY function result as the identity generation is performed only under the current scope, as shown below:
SELECT SCOPE_IDENTITY() AS ScopeID
If we try to insert two more records to the first table:
INSERT INTO ID_Test (EmpName) VALUES ('Zaid'),('Claude')
And check the current identity value for both tables using the IDENT_CURRENT function:
SELECT IDENT_CURRENT('ID_Test') AS ID_TestCurrentID
GO
SELECT IDENT_CURRENT('SecondID_Test') AS SecondID_TestCurrentID
GO
The identity value for the first table will be increased by two and the identity value for the second table will not change:
Checking the @@IDENTITY function result again:
SELECT @@IDENTITY AS IDNT
The result will show the last identity value generated in the current connection:
This is similar to the SCOPE_IDENTITY function result, as the insert statement is performed under the current scope only:
SELECT SCOPE_IDENTITY() AS ScopeID
Which is 6 for the SCOPE_IDENTITY function too:
Assume that we have the below trigger, that inserts a new record to the second table each time we perform an insert operation to the first table:
CREATE TRIGGER IDTrigger ON ID_Test FOR INSERT
AS
BEGIN
INSERT SecondID_Test DEFAULT VALUES
END;
If we try to insert a new record to the first table, the output message will show that there are two records inserted by this INSERT statement. The first is to table ID_Test and the second is to SecondID_Test from the trigger.
Checking the current identity value for both tables using the IDENT_CURRENT function:
SELECT IDENT_CURRENT('ID_Test') AS ID_TestCurrentID
GO
SELECT IDENT_CURRENT('SecondID_Test') AS SecondID_TestCurrentID
GO
The result will show that the identity value for both tables increased by one after the previous insertion process:
Comparing the result of the @@IDENTITY and SCOPE_IDENTITY functions:
SELECT @@IDENTITY AS IDNT
GO
SELECT SCOPE_IDENTITY() AS ScopeID
GO
You will see that the @@IDENTITY function returns the last generated identity value, regardless of the scope that generates that value, this means that it returns the identity value generated by the insert statement performed by the trigger, which is the value inserted to the second table.
But the SCOPE_IDENTITY returns the last identity value generated in the current scope only, which is the value inserted to the first table using the first INSERT statement as shown below:
Finally, if you check the @@IDENTITY and SCOPE_IDENTITY functions results in a separate query window without performing any other operation, the result returned from both functions will be NULL. This is because no change is performed in this session on any table with an IDENTITY column:
SQL Server IDENTITY Function
Another function that works with the identity value is the IDENTITY function. This is different from the IDENTITY property that is used in the CREATE TABLE or ALTER TABLE statements. This function is used to insert identity values into a new table using the SELECT…INTO T-SQL statement.
You need to provide a valid data type for the identity column from the integer data type categories (bit and decimal data types are not allowed). You need to provide the IDENTITY function with the seed and increment values for the identity column and finally the name of the column that will be inserted to the new table.
Assume that we need to create a new table that contains an identity column and the EmpName column from the ID_Test table created previously. The SELECT…INTO T-SQL statement below can be used to create the new table with the required data:
SELECT IDENTITY(int, 1,1) AS Ident, [EmpName]
INTO NewIdenTable
FROM ID_Test;
GO
Checking the new table’s data using the below SELECT statement:
SELECT * FROM NewIdenTable
GO
The seven records from the source table will be inserted to the new table as shown below:
The result returned from the IDENT_CURRENT, @@IDENTITY and SCOPE_IDENTITY functions will be the same for the new table, as all identity values generated are for the same table under the current scope:
SELECT IDENT_CURRENT('NewIdenTable') AS NewIdenTableCurrentID
GO
SELECT @@IDENTITY AS IDNT
GO
SELECT SCOPE_IDENTITY() AS ScopeID
GO
The result will be as follows:
SQL Server OUTPUT Clause
The OUTPUT clause is used mainly to return information about the rows affected by INSERT, UPDATE, DELETE, or MERGE T-SQL commands. You can easily benefit from the captured result by inserting it into another temporary table or table variable to use the data later in your logic. The OUTPUT clause is a good and recommended alternative for the SCOPE_IDENTITY function, as it can be used to return the new identity value that is inserted into a specific table.
Let us truncate the ID_test table created previously, create a temp table to host the inserted identity values and insert four new rows into that table. This time the INSERT statement will contain a new clause, OUTPUT … INTO, that is used to write the ID values that are inserted into the created temp table. The T-SQL script that is used to achieve that will be the following:
USE MSSQLTipsDemo
GO
TRUNCATE TABLE ID_Test
CREATE TABLE #TempIDs (ID INT)
INSERT INTO ID_Test (EmpName) OUTPUT INSERTED.ID INTO #TempIDs VALUES
('Saed'),('Ammar'),('Luai'),('Jaad')
SELECT MAX (ID) AS LastID FROM #TempIDs
DROP TABLE #TempIDs
Recalling that the TRUNCATE statement will delete all records from the table and reset the identity seed value. After inserting four new records, the last inserted identity value within the query scope will be 4 as shown below:
Next Steps
- Take some time to test these examples or other examples in your environment along with these different functions to get a better understanding of how they work and the values that are returned.
- Read more SQL Server Identity Column Tips.
