Identity Column in SQL Server

Identity Column in SQL Server

In this article, I am going to discuss Identity Column in SQL Server with examples. Please read our previous article before proceeding to this article where we discussed Cascading Referential Integrity Constraints in SQL Server with examples. An Identity in SQL Server is a property which can be applied to a column of a table whose value is automatically created by the server. That means as a user we cannot insert a value manually into an identity column in SQL Server.

Syntax IDENTITY [(seed,increment)]

Arguments

Seed: Starting value of a column. The default value is 1.

IncrementIt specifies the incremental value that is added to the identity column value of the previous row. The default value is 1.

We can set the identity property to a column either when the table is created or after table creation. 

The following shows an Identity property when the table is created:

Create Table Person
(
     PersonId int identity(1, 1),
     Name nvarchar(20)
)

The following shows an Identity column after the table has been created:
CREATE TABLE Person
(
  PersonId int,
  Name nvarchar(20)
)
GO

ALTER TABLE Person
DROP COLUMN PersonId;  
GO

ALTER TABLE Person
ADD PersonId INT IDENTITY(1,1); 
GO

We can set an identity property for a column after table creation but for this first, we should drop that column then create a new column with the Identity property.

If a column is marked as an identity column, then the values for this column are automatically generated, when we insert a new row into the table.

The above create table statement marks PersonId as an identity column with seed = 1 and Identity Increment = 1. Seed and Increment values are optional. If we don’t specify the identity increment and seed, then by default both are to 1. 

Example:

Consider the following 2 insert statements, here we only pass the values for Name column. We are not passing the value for PersonId column. 

Insert into Person values (‘Bob’)

Insert into Person values (‘James’)

But, If we select all the rows from the Person table, then we will see that, ‘Bob’ and ‘James’ rows have got 1 and 2 as PersonId.

select * from Person

Identity Column in SQL Server

Now, if I try to execute the following query,

Insert into Person values (1,’Mark’)

When we execute the above insert statement, it will give us the following error

Identity in SQL Server

So if we mark a column as an Identity column, then we don’t need to supply a value for that column explicitly. The value for the identity column is automatically calculated and provided by SQL Server. So, to insert a row into Person table, just provide value for Name column as shown below.

Insert into Person values (‘Mark’)

Now fetch the record from the Person table

Select * from Person

It will give the following result set.

Identity Column in SQL Server

Delete the row, that we have just inserted i.e. the row with PersonId = 3 and insert another row as shown below.

Delete from Person where PersonId = 3

Insert into Person values(‘Smith’)

Now fetch the record from the Person table

Select * from Person

It will give the following result set.

Identity Column in SQL Server

You can see that the value for PersonId is 4. A record with PersonId = 3, does not exist, and you want to fill this gap. To do this, you should be able to explicitly supply the value for the identity column. To explicitly supply a value for identity column

1. First turn on identity insert – SET Identity_Insert Person ON

2. Secondly, you need to specify the identity column name in the insert query as shown below

Insert into Person(PersonId, Name) values(3, ‘Sara’)

Now fetch the record from the Person table

Select * from Person

It will give the following result set.

Identity Column in SQL Server

Let’s execute the below insert query

Insert into Person values(‘Pam’)

Identity in SQL Server

As long as the Identity_Insert is turned on for a table, we need to explicitly provide the value for that column. If we don’t provide the value, we get an error as shown in the above example.

So once we filled the gaps in the identity column, and if we wish the SQL server to calculate the value, turn off Identity_Insert as shown below.

SET Identity_Insert Person OFF

If we have deleted all the rows in a table, and we want to reset the identity column value, use DBCC CHECKIDENT command. This command will reset the PersonId identity column.

Syntax:

DBCC CHECKIDENT(Person, RESEED, 0)

Example:

SET Identity_Insert Person OFF

Delete from PersonDelete all the records from the Person table

DBCC CHECKIDENT(Person, RESEED, 0) Use DBCC command to reset the identity column value

Once we execute the DBCC Command it will give us below output

Checking identity information: current identity value ‘4’.

DBCC execution completed. If DBCC printed some error messages, then just contact with your database administrator.

-- Insert some data into the Person table
Insert into Person values ('Bob')
Insert into Person values ('James')

Select * from Person

It will give us the below result set

Identity Column in SQL Server

How we will get the last generated identity column value in SQL Server?

We already understood that identity column values are auto-generated. There are several ways in SQL Server to retrieve the last identity value that is generated. The most common approach is to use the built-in SCOPE_IDENTITY() function. 

Apart, from using SCOPE_IDENTITY() built-in function, you may also use the built-in @@IDENTITY and IDENT_CURRENT(‘TableName’) function. 

Example:

Let us see some examples by using the above built-in function for getting the last generated identity column value in SQL Server.

Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT('Person')
Let’s now understand the difference between, these 3 approaches.

The SCOPE_IDENTITY() returns the last identity value that is created in the same session (Connection) and in the same scope (in the same Stored procedure, function, trigger).

Let’s say, I have 2 tables Person1 and Person2 and I have a trigger on the Person1 table, which will insert a record into the Person2 table.

Now, when we insert a record into the Person1 table, SCOPE_IDENTITY() returns the identity value that is generated in the Person1 table, whereas @@IDENTITY returns, the value that is generated in the Person2 table.

So @@IDENTITY returns the last identity value that is created in the same session without any consideration to the scope.

The IDENT_CURRENT(‘Person’) returns the last identity value created for a specific table across any session and any scope.

Note: We will discuss this example when we discuss trigger.

In brief:

SCOPE_IDENTITY()

The SCOPE_IDENTITY() built-in function returns the last identity column value that is created within the same session and same scope.

@@IDENTITY

The @@IDENTITY() built-in function returns the last identity column value that is created in the same session but with any scope.

IDENT_CURRENT(‘TableName’)

The IDENT_CURRENT() built-in function returns the last identity column value that is created for a specific table across any session and any scope.

In the next article, I will discuss Sequence Object in SQL Server which is a new feature introduced in SQL server 2012.

SUMMARY

In this article, I try to explain the Identity in SQL Server step by step with a simple example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article. 

Leave a Reply

Your email address will not be published. Required fields are marked *