SQL Reset Identity ID in already populated table

Question

hey all. I have a table in my DB that has about a thousand records in it. I would like to reset the identity column so that all of the ID's are sequential again. I was looking at this but I'm ASSuming that it only works on an empty table

Current Table

ID    |    Name
1           Joe
2           Phil
5           Jan
88          Rob

Desired Table

ID    |    Name
1           Joe
2           Phil
3           Jan
4           Rob

Thanks in advance

Answer 1

Use DBCC CHECKIDENT. the table doesn't need to be empty:

• DBCC CHECKIDENT ( table_name, NORESEED)

Current identity value is not reset. DBCC CHECKIDENT returns the current identity value and the current maximum value of the identity column. If the two values are not the same, you should reset the identity value to avoid potential errors or gaps in the sequence of values.

• DBCC CHECKIDENT ( table_name ) or DBCC CHECKIDENT ( table_name, RESEED )

If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.

• DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )

Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.

If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions:

• If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.

• If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

Answer 2

The easiest way would be to make a copy of the current table, fix up any parentid issues, drop it and then rename the new one.

You could also temporarily remove the IDENTITY and try the folowing:

;WITH TBL AS
(
  SELECT *, ROW_NUMBER(ORDER BY ID) AS RN
  FROM CURRENT_TABLE
)
UPDATE TBL
SET ID = RN

Or, if you don't care about the order of the records, this

DECLARE INT @id;
SET @id = 0;

UPDATE CURRENT_TABLE
SET @id = ID = @id + 1;

Answer 3

Quick solution would be to:

1. create a new table with the same schema
2. copy the old table to the new one (except for the identity column)
3. delete the old table
4. rename the new table

Answer 4

Because you have foreign keys in the same table (per your comment), you will need to preserve the mapping from old to new somewhere and re-instate the foreign keys to match the new identities.

There are a number of approaches for doing this, but I would strongly question the need to update your primary keys, especially since you already have foreign keys referencing them, and it's just a surrogate key. It's not like you are changing your surrogate key to a GUID or something special.

Answer 5

one way, wrap this in a transaction

select id,name into #temp from YourTable

      truncate table YourTable

      insert YourTable (name)
      select name from #temp

Answer 6

Here's how I reset identity fields. The CTE (Common Table Expression) above version is overkill. Just use the current Row Number to update the identity column using a simple update statement with a join:

UPDATE [YourTable] SET ID = rn.RowNumber FROM [YourTable]
JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber
  FROM [YourTable]) rn ON rn.ID = [YourTable].ID

This statement may be refactored to be even simpler. If so, I would love to see the simpler version.

I hope this helps someone.