Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The following operations can be performed with the DBCC CHECKIDENT command.
- The current identitiy value of an identity column can be found.
- The maximum identitiy value of an identitiy column can be found.
- The current identitiy value of an identity column can be set as any number.
- The current identitiy value of an identitiy column can be set as the maximum identitiy value.
First, we create a table with the help of the following script and add a few records into this table.
1 2 3 4 5 6 7 8 | CREATE TABLE [dbo].[MyTable] ([ID] INT IDENTITY(3,2), [Name] VARCHAR(100) NULL ) INSERT [dbo].[MyTable] VALUES ('Hakan GURBASLAR'),('Nurullah CAKIR'),('Faruk ERDEM') GO SELECT * FROM [dbo].[MyTable] |
Find current and maximum identitiy value of an identitiy column:
1 | DBCC CHECKIDENT ( '[dbo].[MyTable]', NORESEED ) |
Set an identity column’s current identity value to any number:
1 2 3 4 5 | DBCC CHECKIDENT ( '[dbo].[MyTable]', RESEED,10 ) INSERT [dbo].[MyTable] VALUES ('This is the first record after the current identity changed.') SELECT * FROM [dbo].[MyTable] |
Set the Current Identitiy value as a lower number:
1 2 3 4 5 | DBCC CHECKIDENT ( '[dbo].[MyTable]', RESEED,8 ) INSERT [dbo].[MyTable] VALUES ('This is the first record after the current identity changed second time.') SELECT * FROM [dbo].[MyTable] |
Let’s check the current and maximum identitiy again:
As you can see, Current identity value is 10 while maximum identity value is 12.
1 | DBCC CHECKIDENT ( '[dbo].[MyTable]', NORESEED ) |
To set the current identity value of an identity column as the maximum identitiy value:
If the current identity value is smaller than the maximum identitiy value, we can set the current identitiy value as the maximum identity value as follows.
1 | DBCC CHECKIDENT ( '[dbo].[MyTable]', RESEED ) |
Check the current and maximum identitiy value again:
1 | DBCC CHECKIDENT ( '[dbo].[MyTable]', NORESEED ) |
