In SQL Server we have command called DBCC CHECKIDENT
Checks the current identity value for
the specified table and, if needed, corrects the identity value.
You can also use DBCC CHECKIDENT 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.
If the table has no rows
and execute below statement
Use [Demo Database NAV (7-1)]
GO
DBCC CHECKIDENT ([CRONUS Nederland BV$Test Increment], NORESEED);
GO
Result:
Checking identity information: current identity
value 'Null', current column value 'Null'.
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.
If it has one row then result of above
statement is:
Checking identity information: current identity
value '1', current column value '1'.
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.
To set the IDENTITY column of a table to
start from 1, the following statement can be issued:
The following example forces the current
identity value in the table to a value of 0. The
next row inserted will use 1 as the value, that is, the new current increment
value defined for the column value plus 1.
Use [Demo Database NAV (7-1)]
GO
DBCC CHECKIDENT ([CRONUS Nederland BV$Test Increment], RESEED,0);
GO
Permissions:
Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
