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

DBCC CHECKIDENT('MyTable', RESEED, 1)

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 work correctly)

Comments

Popular posts from this blog

Clearcase check in/uncheck out all the checked out files script

MSSQL GROUP_CONCAT

Duplicating a local copy of project from TFS and making web software client factory to work