Systems Engineering and RDBMS

Re-set Identity Values

Posted by decipherinfosys on August 5, 2007

SQL Server provides a DBCC (Database Consistency Checker) command: “DBCC CHECKIDENT” to reset the identity value for the column. A few posts back, we had covered how to re-set sequence values in Oracle…this post is on the same lines in SQL Server.

Example:

— USE <YourDatabase>
— GO
CREATE TABLE dbo.Test
(
Test_ID INT IDENTITY(1,1) NOT NULL,
Test_DATE DATETIME,
CONSTRAINT PK_TEST PRIMARY KEY(Test_ID)
)
— FILEGROUP Clause
GO

INSERT INTO Test(Test_Date) VALUES(GETDATE());
INSERT INTO Test(Test_Date) VALUES(GETDATE());

Selecting test_id from test table will give the following result:

TEST_ID
——-
1
2

If you check the current identity value, it should be set to 2:

select ident_current(‘test’)

———-
2

Let us truncate the table and re-insert the statements and see the result set. Truncate table will reset the identity value back to 1. You have to be very careful as truncating table will wipe out all the data from the table…we are doing it to illustrate that truncating re-sets the identity values automatically so in your test or benchmark databases, if you have data creation scripts, truncate command will help in re-setting all the values.

TRUNCATE TABLE TEST;

select ident_current(‘test’)

———-
1

INSERT INTO Test(Test_Date) VALUES(GETDATE());
INSERT INTO Test(Test_Date) VALUES(GETDATE());

Again selecting records from TEST table will produce the same output as shown above.

DBCC CHECKIDENT function can also (in addition to the IDENT_CURRENT() function) be used to check the current identity value in the table and to reseed the identity value back to desired value.

— USE <YourDatabase>
— GO
DBCC CHECKIDENT (TEST,NORESEED)
GO

Above command reports the current identity value in the table without resetting the identity value. Following is the output.

Checking identity information: current identity value ‘2’, current column value ‘2’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You can also use this command to re-seed the value to any number:

— USE <YourDatabase>
— GO
DELETE FROM dbo.TEST
GO
DBCC CHECKIDENT (TEST,RESEED,1000)
GO

Above command will reset the identity value of the column to 1000. Now for newly inserted records identity value will start with 1000.

INSERT INTO Test(Test_Date) VALUES(GETDATE());
INSERT INTO Test(Test_Date) VALUES(GETDATE());
SELECT TEST_ID FROM TEST;

Output will be
TEST_ID
——-
1001
1002

select ident_current(‘test’)

———-
1002

DBCC CHECKIDENT can be used to reset to any identity value you want to start with.

One Response to “Re-set Identity Values”

  1. […] to check for and re-seed identity values in a table in SQL Server. You can access that post over here. One of the questions that a friend had asked yesterday was whether it is an online operation or […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: