SQL Server IDENTITY column

Learning Objective

The objective of this tutorial is to teach you how to use SQL Server IDENTITY column in a table of SQL Server.

Introduction of SQL Server IDENTITY column

Identity column in a table defines a column that values increment automatically. The user does not required to enter the values explicitly for an Identity column. SQL Server takes cares of the values of the column once you define it. An Identity column uniquely identifies a column in a table.

SQL Server IDENTITY column Syntax

The Syntax of IDENTITY column in SQL Server is as follows:

IDENTITY [( seed, increment)]

In this syntax,

  • seed – It defines the starting value of the column. The default value is always 1.
  • increment – It defines the increment of the value for each subsequent row. The default value for this one is also always 1.

As you can see that the default value for both seed and increment of an IDENTITY column is 1 e.g. (1,1). That means the value of the first row would be 1, the value of the second row would be 2, and so on.

Suppose you have defined an IDENTITY column as below:

IDENTITY (5,5);

The above statement generates 5 as the first-row value and 10 for the second-row value, 15 for the third-row value, and so on.

Remember that SQL Server allows only one IDENTITY column in a table.

SQL Server IDENTITY column example

In order to understand the IDENTITY column with more clarity, let’s create a schema named college in SQL Server.

CREATE SCHEMA college;
GO

Next create a table named students with roll_no as IDENTITY column.

CREATE TABLE college.students
(
   roll_no INT PRIMARY KEY IDENTITY,
   first_name nVARCHAR(50) NOT NULL,
   last_name nVARCHAR(50) NOT NULL,
   gender char(1) NOT NULL
);

Now insert a new into the students table as follows.

INSERT INTO college.students values ('Jack','Mill','M');

Output

SELECT * FROM college.students;
sql server identity

You can see clearly from the output that the roll_no column in the first row of students table loaded with one although we haven’t provided any value for that column.

Next, let’s insert another column into the students table.

INSERT INTO college.students values ('Joe','Miller','M');

Now if you run the SELECT query on the table, you will get the following output.

Output

As you can see above that the second row value populated with two by SQL Server.

Reusing of IDENTITY values

SQL Server does not reuse identity values. That means, when you try to insert a row into a table having IDENTITY column and the statement failed or roll back, then the identity value will be lost and will never generate that value again. This creates a gap in value of IDENTITY column.

Let’s take our earlier example again,

First, drop the college.students table using SQL Server DROP TABLE statement and recreate the table again as below:

DROP TABLE college.students;
CREATE TABLE college.students
(
    roll_no INT PRIMARY KEY IDENTITY,
    first_name nVARCHAR(50) NOT NULL,
    last_name nVARCHAR(50) NOT NULL,
    gender char(1) NOT NULL
);

Second, insert a row into the college.students table again and check the data using the SELECT statement.

INSERT INTO college.students values (‘Jack’,’Mill’,’M’);

sql server identity

Third, try to insert another row using the below statement.

INSERT INTO college.students values ('Mike','Taylor','Male');

You can notice that the value of the gender we have provided ‘Male’ which is four characters long but in the table definition we have mentioned that gender column is one character long. Obviously, the above statement fails.

Msg 2628, Level 16, State 1, Line 14
String or binary data would be truncated in table 'master.college.students', column 'gender'. Truncated value: 'M'.
The statement has been terminated.

Now, we rectify the error and executed the insert statement again as follows.

INSERT INTO college.students values (‘Mike’,’Taylor’,’M’);

This time if you check the table you will find a gap in the roll_no column. This is because of the previous failed statement.

Summary

In this tutorial, you have learned how to use SQL Server IDENTITY column in a table of SQL Server.

Advertisement