Reset Database Table Identity Value

One of things that always motivate me to blog is future reference. Anything I place here makes a good reference later. And of course I can always tell people "Hey you can find it here"

So here is today's SQL tip:

you can use the DDBC command to reseed the identity column value. This is usually helpful after a series of DELETE operations. By default MS SQL Server doesn't reseed (give the default seed value + 1) the identity column, so say you had a Users table with 1000 records with an identity column UserID. Then you decided to kick all the users off ( DELETE FROM Users). Then you decided to add them back INSERT INTO Users(.. ) VALUES ( ... ), the first inserted row will have a UserID value of 1001 ( not 1).

DDBC command sytnax:

DBCC CHECKIDENT ( table_name, [reseed / noreseed], [new_seed_value])

So to use it on our Users table, we can do the following:

DBCC CHECKIDENT ( Users, reseed, 0)

I will be soon blogging on the use of FOR XML EXPLICIT. This is not a SQL blog, It is just SQL that I keep forgetting.