about identity_insert reset problem

[alexyxj]

i use identity_insert for a column in one of my tables. when I first insert 5 rows, this column will show number from 1 to 5 (identity set as 1,1), but when I delete this 5 rows and re-insert this 5 rows, the number get incremented (6-10), how can I reset it and make it still start from 1 ??? can I run any simple query to do that?? thanks.

[David Mujica]

The intent of an identity column is that it uniquely defines a row in a table, FOREVER ! You can always create another row in that table with the exact other values, but it will never be known by an identity value previously assigned. It may be possible to re-assign the identity sequence generator, but don't do it. Have you considered what would happen if another table was referencing your master table with this identity column ?

[Wendelius]

For example DBCC CHECKIDENT (Transact-SQL) [^]. However, identity is used to generate surrogate key values so the number generated should always be irrelevant. The database does not quarantee anything but uniqueness.

The need to optimize rises from a bad design. My articles[^]

[Mycroft Holmes]

In case you did not get it - DO NOT CHANGE THE IDENTITY VALUES - this is a basic tennant of relational databases.

Never underestimate the power of human stupidity RAH