#reset identity

[codecavethings-blog]

SQL Delete

SQL Delete and reset identity

Why

We all have to delete data from tables from time to time, and sometimes we want to reset the Identity column, this can be achieved like this:

Theory

The DBCC CHECKIDENT management command is used to reset identity counter. 

The command syntax is:

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ]

Example

DELETE FROM [table_name] DBCC CHECKIDENT ('[table_name]', RESEED, 0); GO