Identity column resetting

[Ivan]

If there are gaps ( due to deletions ) in indentity column, how do I
resequence the values ? As well as how to reset the last used number ?

Info. is greatly appreciated.

Ivan

[Ananth]

From BOL :

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


Arguments
'table_name'
Is the name of the table for which to check the current identity value. Table names must conform to the rules for identifiers. For more information, see Using Identifiers. The table specified must contain an identity column.
NORESEED
Specifies that the current identity value should not be corrected.
RESEED
Specifies that the current identity value should be corrected.
new_reseed_value
Is the value to use in reseeding the identity column.


You cannot resequence the values without recreating the table, copying all the data out and then in again generating new Identity values. The other option is to run a query which will check for gaps and insert rows after
SET IDENTITY_INSERT ON, and specifying the value you want to insert in the insert statement.




------------
Ivan at 5/2/01 8:52:48 AM


If there are gaps ( due to deletions ) in indentity column, how do I
resequence the values ? As well as how to reset the last used number ?

Info. is greatly appreciated.

Ivan