05 September 2014

SQL Server: Reset Field IDENTITY


I often use identity field as primary key to be automatically generated as sequence number.
But sometime I meet a condition to delete all table row, and I want it sequence back to 1. Or deleting several last row and want the next row inserted to be a specific sequence number.

Is it possible? Yes it is. Lets make it clear with an example.
First create a table named tblColor.
CREATE TABLE tblColor
(
ColorID INT IDENTITY,
ColorName VARCHAR(25)
)


Then insert data row using these queries:
INSERT INTO tblColor (ColorName) VALUES ('Purple')
INSERT INTO tblColor (ColorName) VALUES ('Green')


ColorID field values are 1 and 2. Check with this query:
SELECT ColorID, ColorName FROM tblColor

Result:


Well... Lets try to delete all and re-insert a new one.
DELETE FROM tblColor
INSERT INTO tblColor (ColorName) VALUES ('Red')


Check the ColorID value.
SELECT ColorID, ColorName FROM tblColor

Result:

Hey... hey.... ColorID = 3 ???? I want it to be 1...
Okay, lets try this syntax:
TRUNCATE TABLE tblColor -- this will emptying data table
INSERT INTO tblColor (ColorName) VALUES ('Red')
SELECT ColorID, ColorName FROM tblColor 


Result:

Another case without emptying data we want to set current identity into specific value. For example we want the next inserted id to be 25, so we have to set current ID into 24 using this query:
DBCC CHECKIDENT('tblColor',RESEED, 24)
INSERT INTO tblColor (ColorName) VALUES ('Yellow')
SELECT ColorID, ColorName FROM tblColor


Result:

Click here if you like this article.


0 comments: