Using DBCC CHECKIDENT to Fetch current Identity column value of a table or Reset identity column value of a table in Sql Server / DBCC reseed in sql server

Hi, in this post I will explore using DBCC CHECKIDENT Command to manually Set and update the value of an Identity column and check the current identity value for a table in Sql Server.

Note – DBCC stands for Database Console Commands for SQL Server

Syntax to create an identity column

<Column_Name> <Data_Type> IDENTITY(seed,increment)

here ,

seed -  seed specifies the initial value of the identity column.

increment - increment specifies the value to be incremented between each row of insertion.

Script to create Test_Table

CREATE TABLE Test_Table2 (                 TestId bigint identity(1,1),                 Column1 nvarchar(max) )

Get the current value of the identity column of a table

Syntax – DBCC CHECKIDENT ("Tbl_Name", NORESEED);

Example – --Execute the Two Insert Statements as given in the scripts section Insert into Test_Table Values('Alpha') Insert into Test_Table Values('Bravo') DBCC CHECKIDENT ("Test_Table", NORESEED);

Output – Checking identity information: current identity value '2', current column value '2'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. The above DBCC command returns the value of the current Identity, since two Records are Inserted the Identity value is 2 Any new record that will be inserted will have the value - current Identity value plus 1 that is 3

Update the current identity column value to some value

Syntax – DBCC CHECKIDENT ("Tbl_Name",  RESEED,  NewValue);

Example: DBCC CHECKIDENT ("Test_Table",  RESEED,  10);

Output – The Test Table has Two Rows (Since it was inserted with two Records earlier) If we want next record to have identity as 10 we need to run following T SQL script in Query Analyzer. DBCC CHECKIDENT (“Test_Table”, Reseed, 0)

Reset the identity column to zero

Syntax - DBCC CHECKIDENT('Tbl_Name', RESEED,  0)

Example - DBCC CHECKIDENT('Test_Table', RESEED,  0)

Note –

1) If the Identity column is a primary key than if we Reseed the column value to zero then it will be accepted but if we try to insert then we get the following error –

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__Test_Tab__8CC3316003B16C81'. Cannot insert duplicate key in object 'dbo.Test_Table'.

2) If the identity value being being updated is greater than maximum value for the column data type then it will not be permitted.

Links –

1) DBCC CHECKIDENT (Transact-SQL)

https://msdn.microsoft.com/en-IN/library/ms176057.aspx

2) SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity

http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/