SQL Server IDENTITY

Summary: in this tutorial, you will learn how to use the SQL Server IDENTITY property to create an identity column for 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 will have the value of one, the second row will have the value of 2, and so on.

If you want the value of the identity column of the first row to be 10 and the incremental value is 10, you can use the following syntax:

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

In SQL Server, each table has one and only one identity column. Typically, it is the primary key column of the table.

SQL Server IDENTITY column 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)

Output:

The output shows that 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)

Output:

The output shows that 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 fails or is 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 is executed successfully. However, the second one 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:

Resetting the identity column value

To reset the identity’s counter, you use the DBCC CHECKIDENT management command:

DBCC CHECKIDENT ('[TableName]', RESEED, 0);
GOCode language: JavaScript (javascript)

For example:

First, delete all rows from the hr.person table:

DELETE FROM hr.person;Code language: CSS (css)

Second, reset the identity’s counter to zero:

DBCC CHECKIDENT ('hr.person', RESEED, 0);
GOCode language: JavaScript (javascript)

Output:

Checking identity information: current identity value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.Code language: JavaScript (javascript)

The output shows that the current identity value is 4. It reset the value to zero.

Third, insert a new row into the hr.person table:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Jhoan','Smith','F');Code language: JavaScript (javascript)

Output:

person_id
-----------
1

(1 row affected)

The person_id value is 1.

Summary

• Use the SQL Server IDENTITY property to create an identity column for a table.