SQL Server IDENTITY

The IDENTITY keyword is a property in SQL Server. When a table column is defined with an identity property, its value will be auto-generated incremental value. This value is created by the server automatically. Therefore, we can't manually enter a value into an identity column as a user. Hence, if we mark a column as identity, SQL Server will populate it in an auto-increment manner.

Syntax

The following are the syntax to illustrate the use of IDENTITY property in SQL Server:

The above syntax parameters are explained below:

Seed: It indicates the starting value of the row loaded into the table. By default, its value is 1.
Increment: It indicates the incremental value, which is added to the identity value of the last loaded row. By default, its value is 1.

Let us understand this concept through a simple example.

Suppose we have a "Student" table, and we want StudentID to be generated automatically. We have a beginning student ID of 10 and want to increase it by 1 with each new ID. In this scenario, the following values must be defined.

Seed: 10

Increment: 1

CREATE TABLE Student (
 StudentID INT IDENTITY(10, 1) PRIMARY KEY NOT NULL, 
) 

NOTE: Only one identify column is allowed per table in SQL Server.

SQL Server IDENTITY Example

Let us understand how we can use the identity property in the table. The identity property in a column can be set either when the new table is created or after it has been created. Here we will see both cases with examples.

IDENTITY property with new table

The following statement will create a new table with the identity property into the specified database:

CREATE TABLE person (
    PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL,
    Fullname VARCHAR(100) NOT NULL,
    Occupation VARCHAR(50),
    Gender VARCHAR(10) NOT NULL
);

Next, we will insert a new row into this table with an OUTPUT clause to see the auto-generated person id:

INSERT INTO person(Fullname, Occupation, Gender)
OUTPUT inserted.PersonID
VALUES('Sara Jackson', 'HR', 'Female');

Executing this query will display the below output:

This output shows that the first row has been inserted with the value ten in the PersonID column as specified in the table definition identity column.

Let's insert another row into the person table as below:

INSERT INTO person(Fullname, Occupation, Gender)
OUTPUT inserted.*
VALUES('Mark Boucher', 'Cricketer', 'Male'),
('Josh Phillip', 'Writer', 'Male');

This query will return the following output:

This output shows that the second row has been inserted with the value 11 and the third row with the value 12 in the PersonID column.

IDENTITY property with existing table

We will explain this concept by first deleting the above table and create them without identity property. Execute the below statement to drop the table:

Next, we will create a table using the below query:

CREATE TABLE person (
    Fullname VARCHAR(100) NOT NULL,
    Occupation VARCHAR(50),
    Gender VARCHAR(10) NOT NULL
); 

If we want to add a new column with the identity property in an existing table, we need to use the ALTER command. The below query will add the PersonID as an identity column in the person table:

ALTER TABLE person  
ADD PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL; 

Adding value into the identity column explicitly

If we add a new row into the above table by specifying identity column value explicitly, SQL Server will throw an error. See the below query:

INSERT INTO person(Fullname, Occupation, Gender, PersonID)
VALUES('Mary Smith', 'Business Analyst', 'Female', 13);

Executing this query will through the following error:

To insert the identity column value explicitly, we need to first set the IDENTITY_INSERT value ON. Next, execute the insert operation to add a new row into the table and then set the IDENTITY_INSERT value OFF. See the below code script:

SET IDENTITY_INSERT person ON

/*INSERT VALUE*/  
INSERT INTO person(Fullname, Occupation, Gender, PersonID)
VALUES('Mary Smith', 'Business Analyst', 'Female', 14);

SET IDENTITY_INSERT person OFF  
   
SELECT * FROM person;

IDENTITY_INSERT ON lets users to put data into identity columns, while IDENTITY_INSERT OFF prevents them from adding value to this column.

Executing the code script will display the below output where we can see that the PersonID with value 14 is inserted successfully.

IDENTITY Function

SQL Server provides some identity functions for working with the IDENTITY columns in a table. These identity functions are listed below:

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

Let's have a look at the IDENTITY functions with some examples.

@@IDENTITY Function

The @@IDENTITY is a system-defined function that displays the last identity value (maximum used identity value) created in a table for the IDENTITY column in the same session. This function column returns the identity value generated by the statement after inserting a new entry in a table. It returns a NULL value when we execute a query that does not create IDENTITY values. It always works under the scope of the current session. It cannot be used remotely.

Example

Suppose we have the current maximum identity value in the person table is 13. Now we will add one record in the same session that increments identity value by one. Then we will use the @@IDENTITY function to get the last identity value created in the same session.

Here is the full code script:

SELECT MAX(PersonID) AS maxidentity FROM person;

INSERT INTO person(Fullname, Occupation, Gender)
VALUES('Brian Lara', 'Cricket', 'Male');

SELECT @@IDENTITY; 

Executing the script will return the following output where we can see the maximum used identity value is 14.

SCOPE_IDENTITY() Function

The SCOPE_IDENTITY() is a system-defined function to display the most recent identity value in a table under the current scope. This scope can be a module, trigger, function, or a stored procedure. It is similar to the @@IDENTITY() function, except this function only has a limited scope. The SCOPE_IDENTITY function returns NULL if we execute it before the insert operation that generates a value in the same scope.

Example

The below code uses both the @@IDENTITY and SCOPE_IDENTITY() function in the same session. This example will first display the last identity value, then insert one row into the table. Next, it executes both identity functions.

SELECT MAX(PersonID) AS maxid FROM person;

INSERT INTO person(Fullname, Occupation, Gender)
VALUES('Jennifer Winset', 'Actoress', 'Female');

SELECT SCOPE_IDENTITY();
SELECT @@IDENTITY;

Executing the code will display the same value in the current session and similar scope. See the below output image:

Now we will see how both functions are different with an example. First, we will create two tables named employee_data and department using the below statement:

CREATE TABLE employee_data (
 emp_id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL, 
 fullname VARCHAR(20) NULL
)
GO
CREATE TABLE department (
 department_id INT IDENTITY(100, 5) PRIMARY KEY, 
 department_name VARCHAR(20) NULL
); 

Next, we create an INSERT trigger on the employee_data table. This trigger is invoked to insert a row in the department table whenever we insert a row in the employee_data table.

The below query creates a trigger for inserting a default value 'IT' in the department table on each insert query in the employee_data table:

CREATE TRIGGER Insert_Department  
ON employee_data  
FOR INSERT AS   
 BEGIN  
  INSERT INTO department  VALUES ('IT')  
 END; 

After creating a trigger, we are going to insert one record into the employee_data table and see the output of both @@IDENTITY and SCOPE_IDENTITY() functions.

Executing the query will add one row into the employee_data table and generates an identity value in the same session. Once the insert query is executed in the employee_data table, it automatically calls a trigger to add one row in the department table. The identity seed value is 1 for the employee_data and 100 for the department table.

Finally, we execute the below statements that display the output 100 for the SELECT @@IDENTITY function and 1 for the SCOPE_IDENTITY function because they return identity value in the same scope only.

SELECT MAX(emp_id) FROM employee_data
SELECT MAX(department_id) FROM department

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

Here is the result:

IDENT_CURRENT() Function

The IDENT_CURRENT is a system-defined function to display the most recent IDENTITY value generated for a given table under any connection. This function does not consider the scope of the SQL query that creates the identity value. This function requires the table name for which we want to get the identity value.

Example

We can understand it by first opening the two connection windows. We will insert one record in the first window that generates the identity value 15 in the person table. Next, we can verify this identity value in another connection window where we can see the same output. Here is the full code:

1st Connection Window

INSERT INTO person(Fullname, Occupation, Gender)
VALUES('John Doe', 'Engineer', 'Male');
GO
SELECT MAX(PersonID) AS maxid FROM person;

2nd Connection Window

SELECT MAX(PersonID) AS maxid FROM person;
GO
SELECT IDENT_CURRENT('person') AS identity_value;

Executing the above codes in two different windows will display the same identity value.

IDENTITY() Function

The IDENTITY() function is a system-defined function used for inserting an identity column into a new table. This function is different from the IDENTITY property that we use with the CREATE TABLE and ALTER TABLE statements. We can use this function only in a SELECT INTO statement, which is used while transferring data from one table to another.

The following syntax illustrates the use of this function in SQL Server:

If a source table has an IDENTITY column, the table formed with a SELECT INTO command inherits it by default. For example, we have previously created a table person with an identity column. Suppose we create a new table that inherits the person table using the SELECT INTO statements with the IDENTITY() function. In that case, we will get an error because the source table already has an identity column. See the below query:

SELECT IDENTITY(INT, 100, 2) AS NEW_ID, 
    PersonID,
    Fullname,
    Occupation,
    Gender 
INTO person_info
FROM person;

Executing the above statement will return the following error message:

Let's create a new table without identity property using the below statement:

CREATE TABLE student_data (
 roll_no INT PRIMARY KEY NOT NULL, 
 fullname VARCHAR(20) NULL
)

Then, copy this table using the SELECT INTO statement including IDENTITY function as follows:

SELECT IDENTITY(INT, 100, 1) AS student_id,
  roll_no,
  fullname
INTO temp_data
FROM student_data;

Once the statement executes, we can verify it using the sp_help command that displays table properties.

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

If we use this function with the SELECT statement, SQL Server will through 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.

Reusing IDENTITY values

We cannot reuse the identity values in the SQL Server table. When we delete any row from the identity column table, a gap will be created in the identity column. Also, SQL Server will create a gap when we insert a new row into the identity column, and the statement is failed or rolled back. The gap indicates that the identity values are lost and cannot be generated again into the IDENTITY column.

Consider the below example to understand it practically. We already have a person table containing the following data:

Next, we will create two more table named 'position', and 'person_position' using the following statement:

CREATE TABLE POSITION (
	PositionID INT IDENTITY (1, 1) PRIMARY KEY,
	Position_name VARCHAR (255) NOT NULL
);

CREATE TABLE person_position (
	PersonID INT,
	PositionID INT,
	PRIMARY KEY (PersonID, PositionID),
	FOREIGN KEY (PersonID) REFERENCES person (PersonID),
	FOREIGN KEY (PositionID) REFERENCES POSITION (PositionID)
); 

Next, we try to insert a new record into the person table and assign them a position by adding a new row into the person_position table. We will do this by using the transaction statement as below:

BEGIN TRANSACTION
    BEGIN TRY
        -- insert a new row into the person table
        INSERT INTO person (Fullname, Occupation, Gender) 
	 VALUES('Joan Smith', 'Manager', 'Male');

        -- assign a position to a new person
        INSERT INTO person_position (PersonID, PositionID)
        VALUES(@@IDENTITY, 10);
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0  
            ROLLBACK TRANSACTION;  
    END CATCH

    IF @@TRANCOUNT > 0  
        COMMIT TRANSACTION;

The above transaction code script executes the first insert statement successfully. But the second statement failed since there was no position with id ten in the position table. Hence, the entire transaction was rolled back.

Since we have maximum identity value in the PersonID column is 16, the first insert statement consumed the identity value 17, and then the transaction was rolled back. Therefore, if we insert the next row in the Person table, the next identity value will be 18. Execute the below statement:

INSERT INTO person(Fullname, Occupation, Gender)
VALUES('Peter Drucker',' Writer', 'Female');

After checking the person table again, we see that the newly added record contains identity value 18.

Two IDENTITY columns in a single table

Technically, it is not possible to create two identity columns in a single table. If we do this, SQL Server throws an error. See the following query:

CREATE TABLE TwoIdentityTable (
 ID1 INT IDENTITY (10, 1) NOT NULL,
 ID2 INT IDENTITY (100, 1) NOT NULL
) 

When we execute this code, we will see the following error:

However, we can create two identity columns in a single table by using the computed column. The following query creates a table with a computed column that uses the original identity column and decreases it by 1.

CREATE TABLE TwoIdentityTable (
 ID1 INT IDENTITY (10, 1) NOT NULL,
 SecondID AS 10000-ID1,
 Descriptions VARCHAR(60)
)

Next, we will add some data into this table using the below command:

INSERT INTO TwoIdentityTable (Descriptions)
VALUES ('Javatpoint provides best educational tutorials'),
('www.javatpoint.com')

Finally, we check the table data using the SELECT statement. It returns the following output:

We can see in the image how the SecondID column acts as a second identity column, decreasing by ten from the starting value of 9990.

SQL Server's IDENTITY column misconceptions

The DBA user has many misconceptions regarding SQL Server identity columns. The following are the list of most common misconceptions concerning identity columns that would be seen:

IDENTITY column is UNIQUE: According to SQL Server's official documentation, the identity property cannot guarantee that the column value is unique. We must use a PRIMARY KEY, UNIQUE constraint, or UNIQUE index to enforce column uniqueness.

IDENTITY column generates consecutive numbers: Official documentation clearly states that the assigned values in the identity column can be lost upon a database failure or server restart. It can cause gaps in the identity value during insertion. The gap can also be created when we delete the value from the table, or the insert statement is rolled back. The values that generate gaps cannot be used further.

IDENTITY column can't auto-generate existing values: It is not possible for the identity column to auto-generate existing values until the identity property is reseeded by using the DBCC CHECKIDENT command. It allows us to adjust the seed value (starting value of the row) of the identity property. After performing this command, SQL Server will not check the newly created values already present in the table or not.

IDENTITY column as a PRIMARY KEY is enough to identify the row: If a primary key contains the identity column in the table without any other unique constraints, the column can store duplicate values and prevent column uniqueness. As we know, the primary key cannot store duplicate values, but the identity column can store duplicates; it is recommended not to use the primary key and identity property on the same column.

Using the wrong tool to get identity values back after an insert: It is also a common misconception about unawareness of the differences between the @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT, and IDENTITY() functions to get the identity value directly inserted from the statement we have just executed.

Difference between SEQUENCE and IDENTITY

We use both SEQUENCE and IDENTITY for generating auto numbers. However, it has some differences, and the main difference is that identity is table-dependent, whereas sequence is not. Let us summarise their differences into the tabular form:

IDENTITY	SEQUENCE
The identity property is used for a specific table and cannot be shared with other tables.	A DBA defines the sequence object that can be shared among multiple tables because it is independent of a table.
This property auto generates values each time the insert statement is executed on the table.	It uses the NEXT VALUE FOR clause to generate the next value for a sequence object.
SQL Server does not reset the column value of the identity property to its initial value.	SQL Server can reset the value for the sequence object.
We cannot set the maximum value for identity property.	We can set the maximum value for the sequence object.
It is introduced in SQL Server 2000.	It is introduced in SQL Server 2012.
This property cannot generate identity value in decreasing order.	It can generate values in decreasing order.

Conclusion

This article will give a complete overview of IDENTITY property in SQL Server. Here we have learned how and when identity property is used, its different functions, misconceptions, and how it is different from the sequence.

Next TopicSQL Server CONCAT Function

← prev next →