|
To restart or reset the values of the IDENTITY column of a table, the DBCC CHECKIDENT command can be used. The DBCC CHECKIDENT command checks the current identity value for the specified table and, if it is needed, changes the identity value. The DBCC CHECKIDENT can also be used to manually set a new current identity value for the identity column.
The syntax of the DBCC CHECKIDENT command is as follows:
DBCC CHECKIDENT ( <table_name> [ , { NORESEED | { RESEED [, <new_reseed_value> ] } } ] )
[ WITH NO_INFOMSGS ]
The <table_name> parameter is the name of the table for which to check the current identity value and it must contain an identity column. The NORESEED clause specifies that the current identity value should not be changed. The RESEED clause specifies that the current identity value should be changed. The <new_reseed_value> is the new value to be used as the current value of the identity column. Lastly, the WITH NO_INFOMSGS clause suppresses all informational messages.
To set the IDENTITY column of a table to start from 1, the following statement can be issued:
DBCC CHECKIDENT ( '[dbo].[Customers]', RESEED, 1 )
This statement should be used only if no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement. If the table already has rows before and the rows were removed using the DELETE statement, the following statement should be used instead:
DBCC CHECKIDENT ( '[dbo].[Customers]', RESEED, 0 )
|