Checks the current identity value for the specified table and, if it’s needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.
Examples
A. Resetting the current identity value, if it’s needed
The following example resets the current identity value, if it’s needed, of the specified table in the AdventureWorks2012 database.SQLCopy
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType');
GO B. Reporting the current identity value
The following example reports the current identity value in the specified table in the AdventureWorks2012 database, and doesn’t correct the identity value if it’s incorrect.SQLCopy
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', NORESEED);
GO C. Forcing the current identity value to a new value
The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value – the new current identity value defined for the column plus 1 (which is the column’s increment value).SQLCopy
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO D. Resetting the identity value on an empty table
The following example forces the current identity value in the ErrorLogID column in the ErrorLog table to a value of 1, after deleting all records from table. Because the table has no existing rows, the next row inserted will use 1 as the value, that is, the new current identity value, without adding the increment value defined for the column.SQLCopy
USE AdventureWorks2012;
GO
TRUNCATE TABLE dbo.ErrorLog
GO
DBCC CHECKIDENT ('dbo.ErrorLog', RESEED, 1);
GO Sources:
Comments