Home > SQL Server > Modify the current identity value in a table using DBCC CHECKIDENT – SQL Identity – SQL Server

Modify the current identity value in a table using DBCC CHECKIDENT – SQL Identity – SQL Server


DBCC CHECKIDENT (): This statement is used to check the current identity value of a table. This function also used to reset the current seed value or re-assign new seed value to an identity column.

Function Syntax

DBCC CHECKIDENT
(
table_name
[, { NORESEED | { RESEED [, new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]

Arguments and examples to use this command

We can use this function in ways using the function arguments. NoReSeed, ReSeed with new value and No_Infomsgs are the optional value that we can use with executing the command.

Table_name: This is the valid table name used in this command within single quotes.

eg: DBCC CHECKIDENT ( ‘Table_name’)

NoReSeed: It will return the current identity value and the latest maximum identity value of an identity column in a table. by default, these two values should be same. otherwise, use reseed to do so.

NoReSeed: This argument will reset the identity value if the identity value is less than the latest maximum identity value.

eg: DBCC CHECKIDENT ( ‘Table_name’, RESEED ) or DBCC CHECKIDENT ( ‘Table_name’)

ReSeed with new Seed value: This argument will reinitialize the seed value of an identity column in a table.

eg: DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )

WITH NO_INFOMSGS : This argument value  help to Suppresses all informational messages from the command.

Example

In this demo ,  I am going to fill the identity value gap in a table.

Step 1: Create a Table for this demo

CREATE TABLE tblIdentity
(
RowID INT IDENTITY,
Code VARCHAR(20)
)
GO

Step 2: Insert 5 rows in to the table using batch execution

INSERT INTO tblIdentity VALUES (‘SQL Server Rider’)
GO 5

Step 3: List or select all value from the table

SELECT * FROM tblIdentity
GO

Step 4: Delete a row that has the identity value 3

DELETE FROM tblIdentity WHERE RowID = 3
GO

Step 5: Check the current identity value in the table

DBCC CHECKIDENT(‘tblIdentity’, NORESEED)
GO

Step 6: Now, reseed the identity value to fill the gap. In this demo, the gap value is 3.

DBCC CHECKIDENT(‘tblIdentity’, RESEED, 2)
GO

Step 7: Check the current identity values. Now, we can see two different values in the current identity value and the current identity column value.

DBCC CHECKIDENT(‘tblIdentity’, NORESEED)
GO

Step 8: Insert a row to fill the gap.

INSERT INTO tblIdentity VALUES (‘SQL Server Rider’)
GO

Step 9: Now, execute this command to reset the identity value to the actual or correct value.

DBCC CHECKIDENT(‘tblIdentity’, RESEED, 5)
GO

or

DBCC CHECKIDENT(‘tblIdentity’, RESEED)

Output

Query execution

pic1Result

pic2Query execution Messages

pic3

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: