Identity in SQL Server – What is it and how to use or reset it’s value?

Identity property is a property of SQL Server which can be defined on a column of a table. Once a column of a table is defined as Identity, SQL Server automatically computes a value of each row inserted in that column. This auto computed values are guaranteed to be unique with in the column/table. Because of this nature, many developers tend to use this property to generate unique value automatically.

A column can be declared as Identity using TSQL or GUID. However the data type of the column should be int/bigint/tinyint, not null and each table can have only one identity column. Use below syntax to declare a column as identity with create or alter statement:

[Column Name] [int] IDENTITY([seed],[increment]) NOT NULL

Seed is the starting value of the column (the value that is used for the very first row loaded into the table) and increment is the incremental value that is added to the identity value of the previous row that was loaded. Default value is (1,1) unless otherwise specified.

However before using identity in your database design, you should be aware of below possible situations:

1. You do not have any direct control on the values of this column. For example, let us assume you have an employee table and decided to define unique employee number as identity. Imagine a situation where for some reason you need to delete a specific employee details from the table (technically delete a row) and then reinsert the details again (technically inserting a row). In this case, there is no way to automatically assign the same employee number to this employee. (SQL Server will give the next available identity value as Employee number to this employee)
2. Deletion of rows can create a gap in the values of the identity column. For example, let us consider the employee table again. Let us say you delete employee number 20 to 30 while 30 was the last value. Still new employees will get employee number from 31 onwards which will create a gap in the values of identity column.

So if you find these issues are major issues for your business needs or if you think your table will experience frequent deletion, may be using “identity” is not a good idea in your design.

However you can still control values of identity column using dbcc command per below details:

How to report the current value of identity?

Use below code:

USE [Database Name]

GO

DBCC CHECKIDENT ("[Table Name]", NORESEED);

GO

Note: This will not change the identity value.

How to reset the current identity value?

Use below code:

USE [Database Name]

GO

DBCC CHECKIDENT ("[Table Name]");

GO

Note: This will reset the identity value from the next available value. So for example in our Employee table, if you delete employee number 20 to last row onwards and then add new employees, new employees will get employee number 20 onwards provided you executed this code to reset current identity value.

How to force the current identity value to a new value?

Use below code:

USE [Database Name]

GO

DBCC CHECKIDENT ("[Table Name], RESEED, [New value]");

GO

Note: This will force current identity value to a new value. So for example in our Employee table, let us consider your last employee number is 99 but for some reason you want to issue new employees “employee number” from 500 onwards. In that case, you can use this code to force current identity value (which is 99) to the desired one (which is 299).