How to Reseed a SQL Server Identity Column

In SQL Server you can specify a column as an Identity column (ColumnName int IDENTITY(1,1) NOT NULL) to have SQL Server automatically set the value of this column upon insert by incrementing a seed value.  This works great but I have run into situations where I wanted to change what identity value is assigned during the next insert. 

What had happened is that this table in question had the primary key column set as an Identity column.  There were only a couple thousand rows in the table but SQL Server was handing out identity values in the 10-million range.  In most cases this would not be a big deal, but for this table the identity value was actually used by customers so a eight digit number was not as easy to remember and repeat as a 4-digit number. 

What had happened is that a row had been inserted into the table with a high identity value (over 10 million) and then SQL Server took over from there and handed out new identity values that were incremented from that number – 23000001, 23000002, 23000003, etc...

To fix this, I was able to change the seed of the Identity field by using a DBCC CHECKIDENT statement.

You can use this DBCC CHECKIDENT statement to check the current identity value:

DBCC CHECKIDENT('table_name', NORESEED)

And then you can use this DBCC CHECKIDENT statement to change the identity value to another value:

DBCC CHECKIDENT('table_name', RESEED, 2300)

After making this change new records inserted into the table now were assigned 4-digit values and were much easier for customers to remember and use.