Showing posts from August, 2008

SQL server IDENTITY and seed

One can make a column to be auto increment by setting it as identity.

CREATE TABLE dbo.MyTable ( MyID int identity(1,1), MyName nvarchar(20) )

One can make it start from certain number by setting the seed

identity(1000, 1)

So, the first row would have id 1001

One can also increase the seed even with some data in the table say already the record run up to 102. If set seed to 200, the next record id would be 201. (one catch is if seed is 1, it will start with 1. But if seed as 11, it will start at 12.)

Also one note is that the seed can not be smaller than the existing one as it would not make any changes. (the existing seed number can be check in the property of the column)

For reseting the identity seed. The DBCC command is needed like


It will attempt to start over from 1 and if found some ID with the same number exist, it will skip that ID and use next number. (This is done by the MSSQL in best effort mode. It is not predictable and guaranteed to …