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

CREATE TABLE MyBlogs
      (
    BlogId          int IDENTITY(1,1) NOT NULL,
    BlogTitle       nvarchar(255) NOT NULL,
    BlogDescription nvarchar(1000) NOT NULL,
    BlogDetails     NVARCHAR(MAX) NOT NULL,
    IsPrimary       bit NOT NULL,
    IsActive        bit NOT NULL
    )

SQL query to insert data in the table

insert into MyBlogs
(BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
values
('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)

SQL query to select all records from the table

select * from MyBlogs

What is identity seed?

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



What is identity increment value?

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



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

Identity Seed with negative number

CREATE TABLE MyBlogs2
   (
    BlogId          int IDENTITY(-100,1) NOT NULL,
    BlogTitle       nvarchar(255) NOT NULL,
    BlogDescription nvarchar(1000) NOT NULL,
    BlogDetails     NVARCHAR(MAX) NOT NULL,
    IsPrimary       bit NOT NULL,
    IsActive        bit NOT NULL
    )


insert into MyBlogs2
(BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
values
('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)

select * from MyBlogs2

Identity Seed with negative number and Negative increment value

CREATE TABLE MyBlogs3
   (
    BlogId          int IDENTITY(-1000,-5) NOT NULL,
    BlogTitle       nvarchar(255) NOT NULL,
    BlogDescription nvarchar(1000) NOT NULL,
    BlogDetails     NVARCHAR(MAX) NOT NULL,
    IsPrimary       bit NOT NULL,
    IsActive        bit NOT NULL
    )

    insert into MyBlogs3
(BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
values
('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)

select * from MyBlogs3

Can we use any other positive value for Identity Increment and Identity seed?
Yes.

CREATE TABLE MyBlogs4
   (
    BlogId          int IDENTITY(5001,10) NOT NULL,
    BlogTitle       nvarchar(255) NOT NULL,
    BlogDescription nvarchar(1000) NOT NULL,
    BlogDetails     NVARCHAR(MAX) NOT NULL,
    IsPrimary       bit NOT NULL,
    IsActive        bit NOT NULL
    )

    insert into MyBlogs4
(BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
values
('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)

select * from MyBlogs4

What is different between identity key and primary key?

We can use the same column as a primary key and identity both

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

CREATETABLE MyBlogs5
    (
        BlogId intIDENTITY(5001, 10) primarykeyNOTNULL,
        BlogTitle nvarchar(255) NOTNULL,
    )

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

CREATE TABLE MyBlogs6
   (
    BlogTitle       nvarchar(255) NOT NULL,
    BlogDescription nvarchar(1000) NOT NULL,
    BlogDetails     NVARCHAR(MAX) NOT NULL,
    IsPrimary       bit NOT NULL,
    IsActive        bit NOT NULL
    )

    insert into MyBlogs6
(BlogTitle, BlogDescription, BlogDetails,IsPrimary,IsActive)
values
('BlogTitle1', 'BlogDescription1' ,'BlogDetails1', 0, 1),
('BlogTitle2', 'BlogDescription2' ,'BlogDetails2', 0, 1),
('BlogTitle3', 'BlogDescription3' ,'BlogDetails3', 0, 1),
('BlogTitle4', 'BlogDescription4' ,'BlogDetails4', 0, 1),
('BlogTitle5', 'BlogDescription5' ,'BlogDetails5', 0, 1)

select * from MyBlogs6

Now add identity key in this table,

ALTERTABLE MyBlogs6 ADDBlogIdINTIDENTITY(500, 5);
select * from MyBlogs6

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