Identity Column: Explanation and reset identity value!!!

Identity property in SQL Server will helps in creating a Identity Column on a table. This property can be used to uniquely identify the records.

We can able to create identity column using keyword: IDENTITY when creating the table or altering the table.

Lets walk with a example to understand how to create the IDENTITY column which generate incremental value as when the records are getting inserted.

• Create table Customer (ID INT Identity, Username Varchar(5), Pwd Varchar(10)) 

Appending the keyword Identity while creating the table will enable Identity values incremented automatically as when the records gets inserted inside ID column.

Lets insert 2 values and check how it is working. 

Insert into Customer (Username,Pwd) Values ('dhina','dhina')

Insert into Customer  (Username,Pwd) Values ('dhinakaran','dhinakaran')

The problem when we execute this statement is, the second Insert will fail as it is the length of the data supplied is more. Truncation error occurs.

To check the IDENTITY Status on table, We can use DBCC CHECKIDENT command on our SQL Server.

As we can see in Snippet, we are though having only one value inserted, but the DBCC CHECKIDENT command resulted that 2 Identity values are created.

Now lets examine this by Inserting a new value to Customer Table.

Insert into Customer  (Username,Pwd) Values ('Sam','Sam')

Now we see the output, we find Identity value of ID Column is starting from 3. So we need to reset the values to Start from 2.

This can be achieved by running the following code:

DBCC CHECKIDENT ('dbo.Customer', RESEED, 0);

and hence, the values are reset to start from 0.

Please refer my other blog on how to insert Duplicate value on table having Identity Column, here

Say if we don't want to drop/truncate the entire table but want to reset the values from 1 in the ID column, then there are two ways which implicitly means/functions the same.

1. Using the SSMS Designer. (Drop the ID column only and recreate ID column with IDENTITY as the constraint)
2. Using the query. (using the alter statement to drop and recreate the ID column).

Follow this code to alter to drop and recreate the ID values where the values are restored.

ALTER TABLE customer DROP COLUMN ID

ALTER TABLE Customer ADD ID INT Identity (1,1)

Hope this helps!!!! 

Interesting fact

If I reseed value with some random number followed by the truncate command on the table what will be the seed value of the table.

Example, if original seed value is 30 and I reseed the value to 1. If I follow up with truncate table what will be the seed value now?

Let us try with some example:

USE [TempDB]

GO

-- Create Table

CREATE TABLE [dbo].[TestTable] (

            [ID] [int] IDENTITY(30, 1) NOT NULL,

            [var] [Varchar](10) NULL

            ) ON [PRIMARY]

GO

-- Build sample data

INSERT INTO [TestTable]

VALUES ('val')

GO

-- Select Data

SELECT *

FROM [TestTable]

GO

 

Now let us reseed the values to 1

-- Reseed

DBCC CHECKIDENT (

                        'TestTable',

                        RESEED,

                        1

                        )

GO

-- check again

-- Build sample data

INSERT INTO [TestTable]

VALUES ('val')

GO

-- Select Data

SELECT *

FROM [TestTable]

GO

If we truncate the table and when we try to reseed it, we may observe, it takes the base property table. The base property of table during table creation had seed value as 30 and not as 1.

GO

-- Truncate table

TRUNCATE TABLE [TestTable]

GO

-- Build sample data

INSERT INTO [TestTable]

VALUES ('val')

GO

-- Select Data

SELECT *

FROM [TestTable]

GO

-- Clean up

DROP TABLE [TestTable]

GO