SQL Server identity is a very simple and useful feature but many of the .NET developers misinterpret it. This year I am little bit involved in interviewing the . ET candidates. The requirement for .NET was that he/she must have basic knowledge of SQL Server as well but more than 70% candidates didn't answer the basic questions of SQL server. That's why I decided to write on SQL Server. In this article I'll explain you the SQL Server Identity in Question/Answer style.

SQL Server Identity key is also known as SQL AUTO INCREMENT Field because in most of the cases we use it for auto Increment field. Let’s try to understand using an example:

SQL Query to create a table

  1. CREATE TABLE MyBlogs
  2. (
  3. BlogId int IDENTITY(1,1) NOT NULL,
  4. BlogTitle nvarchar(255) NOT NULL,
  5. BlogDescription nvarchar(1000) NOT NULL,
  6. BlogDetails NVARCHAR(MAX) NOT NULL,
  7. IsPrimary bit NOT NULL,
  8. IsActive bit NOT NULL
  9. )

SQL query to insert data in the table

  1. insert into MyBlogs
  2. (BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
  3. values
  4. ('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
  5. ('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
  6. ('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
  7. ('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
  8. ('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)

SQL query to select all records from the table

  1. select * from MyBlogs
all

What is identity seed?

Identity Seed is the value that is used to decide the first value of identity in the table.

identity seed

What is identity increment value?

Identity incremental value is the value which is added to the Identity value of the previous row.

identity increment value

Can we use negative values for identity seed and increment value?
Yes

Identity Seed with negative number
  1. CREATE TABLE MyBlogs2
  2. (
  3. BlogId int IDENTITY(-100,1) NOT NULL,
  4. BlogTitle nvarchar(255) NOT NULL,
  5. BlogDescription nvarchar(1000) NOT NULL,
  6. BlogDetails NVARCHAR(MAX) NOT NULL,
  7. IsPrimary bit NOT NULL,
  8. IsActive bit NOT NULL
  9. )
  12. insert into MyBlogs2
  13. (BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
  14. values
  15. ('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
  16. ('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
  17. ('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
  18. ('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
  19. ('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)
  21. select * from MyBlogs2

result

Identity Seed with negative number and Negative increment value
  1. CREATE TABLE MyBlogs3
  2. (
  3. BlogId int IDENTITY(-1000,-5) NOT NULL,
  4. BlogTitle nvarchar(255) NOT NULL,
  5. BlogDescription nvarchar(1000) NOT NULL,
  6. BlogDetails NVARCHAR(MAX) NOT NULL,
  7. IsPrimary bit NOT NULL,
  8. IsActive bit NOT NULL
  9. )
  11. insert into MyBlogs3
  12. (BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
  13. values
  14. ('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
  15. ('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
  16. ('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
  17. ('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
  18. ('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)
  20. select * from MyBlogs3

increment

Can we use any other positive value for Identity Increment and Identity seed?
Yes.
  1. CREATE TABLE MyBlogs4
  2. (
  3. BlogId int IDENTITY(5001,10) NOT NULL,
  4. BlogTitle nvarchar(255) NOT NULL,
  5. BlogDescription nvarchar(1000) NOT NULL,
  6. BlogDetails NVARCHAR(MAX) NOT NULL,
  7. IsPrimary bit NOT NULL,
  8. IsActive bit NOT NULL
  9. )
  11. insert into MyBlogs4
  12. (BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
  13. values
  14. ('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
  15. ('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
  16. ('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
  17. ('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
  18. ('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)
  20. select * from MyBlogs4


positive

What is different between identity key and primary key?

We can use the same column as a primary key and identity both
  1. BlogId int IDENTITY(5001,10) primary key NOT NULL
But if we consider primary key and identity separately then there are few differences between primary key and Identity.

Identity is auto generated and auto incremented value whereas user can pass the value for primary key.

Identity can use only numeric values whereas primary key can use other data types also.

Both are unique and can be created only one per table but when primary key is created on a table then a clustered index is created automatically where as in case of identity no cultured index is created automatically.

Example
  1. CREATETABLE MyBlogs5
  2. (
  3. BlogId intIDENTITY(5001, 10) primarykeyNOTNULL,
  4. BlogTitle nvarchar(255) NOTNULL,
  5. )
example
Can Identify value be reseeded?

Yes, Identity value is reseeded in many cases.

It can be reseeded by dbcc command.

It is also reseeded when truncate command run on a table.

Can Identity be created after creation of table?
Yes
Can we create an identity key on a table which is having data already?
Yes
  1. CREATE TABLE MyBlogs6
  2. (
  3. BlogTitle nvarchar(255) NOT NULL,
  4. BlogDescription nvarchar(1000) NOT NULL,
  5. BlogDetails NVARCHAR(MAX) NOT NULL,
  6. IsPrimary bit NOT NULL,
  7. IsActive bit NOT NULL
  8. )
  10. insert into MyBlogs6
  11. (BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
  12. values
  13. ('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
  14. ('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
  15. ('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
  16. ('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
  17. ('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)
  19. select * from MyBlogs6

data

Now add identity key in this table,
  1. ALTERTABLE MyBlogs6 ADDBlogIdINTIDENTITY(500, 5);
  2. select * from MyBlogs6
result

Can we specify Identity settings for a table while creating a table using wizards?
Yes.

wizards