When removing entries in SQL Server, reset the identity seed

Question

I've added records to a SQL Server database table. The table had a primary key established and the auto-increment identity seed is set to "Yes". This is done mostly because, with SQL Azure, each table must have a primary key and an identity declared.

Nevertheless, because I need to remove certain items from the table, the identification seed for those tables will be disrupted, as will the index column (which is auto-generated with an increment of 1).

How can I reset the identity column after deleting the entries such that the column's sequence is in ascending numerical order?

The identify field is not utilized as a foreign key anywhere in the database.

Answer 1

Sounds like you should consider not having that column at all, and making a different column the primary key (surely there is something else that is unique and identifies a row?).

I don't recall there ever being a requirement that every table have an IDENTITY column in any flavor of SQL Server.

If you don't care about what identity value is actually associated with any given row, you can always generate a virtual identity column on the fly (perhaps in a view):

SELECT This_Identity_Column_Does_Not_Exist = ROW_NUMBER()
  OVER (ORDER BY The_Column(s)_Ascending), ... other cols ...
FROM dbo.Table_Without_An_Unnecessary_Identity_That_Only_Causes_Pain;