SQL Server Identity

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(MAXNOT 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(MAXNOT NULL,   
  7.     IsPrimary       bit NOT NULL,  
  8.     IsActive        bit NOT NULL  
  9.     )   
  10.   
  11.   
  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)  
  20.   
  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(MAXNOT NULL,   
  7.     IsPrimary       bit NOT NULL,  
  8.     IsActive        bit NOT NULL  
  9.     )   
  10.   
  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)  
  19.   
  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(MAXNOT NULL,   
  7.     IsPrimary       bit NOT NULL,  
  8.     IsActive        bit NOT NULL  
  9.     )   
  10.   
  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)  
  19.   
  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(MAXNOT NULL,   
  6.     IsPrimary       bit NOT NULL,  
  7.     IsActive        bit NOT NULL  
  8.     )   
  9.   
  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)  
  18.   
  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