On SQL Server, to reset an identity column we can use the DBCC CHECKIDENT (that checks the current identity value for the specified table in SQL Server and, if it is needed, changes the identity value).

The syntax for the command is the following:

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

For example, if we have a table called ‘TABLE_NAME’, to reset the identity column of this table to 0, we can execute:

DBCC CHECKIDENT ('TABLE_NAME', RESEED, 0)

Now if we add a new row on the table, the identity column contains 1 and so on.

If we want that our column start from another value, for example 5, we can execute the command with new_reseed_value = 4:

DBCC CHECKIDENT ('TABLE_NAME', RESEED, 4)

Now if we add a new row on the table, the identity column contains 5 and so on.

Tagged:

Laisser un commentaire