Friday, May 3, 2013

SQL Server Compact Code Snippet of the Week #13 : reseed (reset) an IDENTITY column

A question that keeps re-appearing in the forum is, how can I reseed/reset an IDENTITY column in SQL Server Compact, as no DBCC command is available. You can simply use a special syntax of the ALTER TABLE command to do this, as follows:

ALTER TABLE [MyTableName] ALTER COLUMN [Id] IDENTITY (1, 1)

This will make the value of the next generated IDENTITY value 1 and increment with 1. Notice that you do not specify the column type, only the IDENTITY specification.

2 comments:

David Swindells said...

Cool, I was just looking for this now. Thanks Erik!

Shoaib Merchant said...

Thank you! You sir are a lifesaver!