SQL Server Identity

Summary: in this tutorial, you will learn how to use the SQL Server IDENTITY property to add an identity column to a table.

Introduction to SQL Server IDENTITY column

To create an identity column for a table, you use the IDENTITY property as follows:

IDENTITY[(seed,increment)]
Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • The seed is the value of the first row loaded into the table.
  • The increment is the incremental value added to the identity value of the previous row.

The default value of seed and increment is 1 i.e., (1,1). It means that the first row, which was loaded into the table, will have the value of one, the second row will have the value of 2 and so on.

Suppose, you want the value of the identity column of the first row is 10 and incremental value is 10, you use the following syntax:

IDENTITY (10,10)
Code language: SQL (Structured Query Language) (sql)

Note that SQL Server allows you to have only one identity column per table.

SQL Server IDENTITY example

Let’s create a new schema named hr for practicing:

CREATE SCHEMA hr;
Code language: SQL (Structured Query Language) (sql)

The following statement creates a new table using the IDENTITY property for the personal identification number column:

CREATE TABLE hr.person (
    person_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

First, insert a new row into the person table:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('John','Doe', 'M');
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

SQL Server Identity Column Example

As can be seen clearly from the output, the first row has been loaded with the value of one in the person_id column.

Second, insert another row into the person table:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Jane','Doe','F');
Code language: SQL (Structured Query Language) (sql)

Here is the output:

SQL Server Identity Column Example 2

As you can see clearly from the output, the second row has the value of two in the person_id column.

Reusing of identity values

SQL Server does not reuse the identity values. If you insert a row into the identity column and the insert statement is failed or rolled back, then the identity value is lost and will not be generated again. This results in gaps in the identity column.

Consider the following example.

First, create two more tables in the hr schema named position and person_position:

CREATE TABLE hr. POSITION (
	position_id INT IDENTITY (1, 1) PRIMARY KEY,
	position_name VARCHAR (255) NOT NULL,

);

CREATE TABLE hr.person_position (
	person_id INT,
	position_id INT,
	PRIMARY KEY (person_id, position_id),
	FOREIGN KEY (person_id) REFERENCES hr.person (person_id),
	FOREIGN KEY (position_id) REFERENCES hr. POSITION (position_id)
);Code language: SQL (Structured Query Language) (sql)

Second, insert a new person and assign this new person a position by inserting a new row into the person_position table:

BEGIN TRANSACTION
    BEGIN TRY
        -- insert a new person
        INSERT INTO hr.person(first_name,last_name, gender)
        VALUES('Joan','Smith','F');

        -- assign the person a position
        INSERT INTO hr.person_position(person_id, position_id)
        VALUES(@@IDENTITY, 1);
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0  
            ROLLBACK TRANSACTION;  
    END CATCH

    IF @@TRANCOUNT > 0  
        COMMIT TRANSACTION;
GO
Code language: SQL (Structured Query Language) (sql)

In this example, the first insert statement was executed successfully. However, the second one was failed due to no position with id one in the position table. Because of the error, the whole transaction was rolled back.

Because the first INSERT statement consumed the identity value of three and the transaction was rolled back, the next identity value will be four as shown in the following statement:

INSERT INTO hr.person(first_name,last_name,gender)
OUTPUT inserted.person_id
VALUES('Peter','Drucker','F');
Code language: SQL (Structured Query Language) (sql)

The output of the statement is:

In this tutorial, you have learned how to use the SQL Server IDENTITY property to create an identity column for a table.

Was this tutorial helpful?