How to Reset Identity Column Values in Sql Server

[Origin]: http://www.c-sharpcorner.com/blogs/how-to-reset-identity-column-values-in-sql-server1

Step 1: Create table.

CREATE TABLE dbo.Emp
(
  ID INT IDENTITY(1,1),
  Name VARCHAR(10)
)

Step 2: Insert some sample data.

INSERT INTO dbo.Emp(name)
VALUES ('Rakesh')
INSERT INTO dbo.Emp(Name)
VALUES ('Rakesh Kalluri')

build status
When we run above query the second insert statement will failed because of varchar(10) length.

Step 3: Check the identity column value.

DBCC CHECKIDENT (<span class="string">'Emp'</span>)

error
Even second insert was failed but the identity value is increased .if we insert the another record the identity value is 3.

INSERT INTO dbo.Emp(Name)
VALUES ('Kalluri')

SELECT * FROM Emp

id name

Step 4: Reset the identity column value.

DELETE FROM EMP WHERE ID=3

DBCC CHECKIDENT ('Emp', RESEED, 1)

INSERT INTO dbo.Emp(Name)
VALUES ('Kalluri')

SELECT * FROM Emp

id name table

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s