DBCC CHECKIDENT in Microsoft SQL Server

DBCC CHECKIDENT

Checks the current identity value for the specified table and, if needed, corrects the identity value.

Syntax

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

Explain with different parameter
DBCC CHECKIDENT (‘table_name’, NORESEED): The current identity value is not reset. DBCC CHECKIDENT returns a report indicating the current identity value and what it should be.

DBCC CHECKIDENT (‘table_name’) or DBCC CHECKIDENT (‘table_name’, RESEED) : If the current identity value for a table is lower than the maximum identity value stored in the column, it is reset using the maximum value in the identity column.

DBCC CHECKIDENT (‘table_name’, RESEED,new_reseed_value) : he current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_valueas the identity. Otherwise, the next row inserted will usenew_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.

Execute DBCC CHECKIDENT (‘table_name’, NORESEED) to determine the current maximum value in the column, and then specify that as the new_reseed_value in a DBCC CHECKIDENT (‘table_name’, RESEED, new_reseed_value) statement.

Execute DBCC CHECKIDENT (‘table_name’, RESEED, new_reseed_value) with new_reseed_value set to a very low value, and then run DBCC CHECKIDENT (‘table_name’, RESEED).

Examples
A. Reset the current identity value, if needed
This example resets the current identity value, if needed, of the jobs table.

USE pubs
GO
DBCC CHECKIDENT (jobs)
GO

B. Report the current identity value
This example reports the current identity value in the jobs table, and does not correct the identity value, if incorrect.

USE pubs
GO
DBCC CHECKIDENT (jobs, NORESEED)
GO

C. Force the current identity value to 30
This example forces the current identity value in the jobs table to a value of 30.

USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO

 

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.