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

Bentham

Is your Identity column used as a foreign key elsewhere? – Shrewsbury

nope, it's just unique because there is another field in the table that references it. – Bentham

IE: ID | ParentID | Name – Bentham

@rockinthesixstring so you're saying it it does have a foreign key within the same table. – Truditrudie

What is the purpose of resetting the IDs? – Baikal

Is 'name' unique, and do you want the new id to be an auto-generated value? – Crosscut

It's not a huge deal really. Name is NOT unique, I just wanted a clean DB when I actually launch the new site. It's a listing of every major city in North America. The ID is unique, the ParentID is obviously the Parent. IE: Los Angeles would be 1|0|Los Angeles and California would be 2|1|California and San Fransisco would be 2|1|San Fransisco. I had to work on some code to do a bulk import, and I hit the DB about 20 times resulting in the current ID's reaching like 100,000... so I'd just like to bring it all back in order is all. If it's too hard, I'll leave it. – Bentham

@rockinthesixstring Try not to get too attached to your identity columns. – Truditrudie

Answer 1

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;

Ganja

Answer 2

one way, wrap this in a transaction

select id,name into #temp from YourTable

      truncate table YourTable

      insert YourTable (name)
      select name from #temp

Ileneileo

very nice! elegant and simple. – Shrewsbury

but it doesn't fixup your ParentID. – Crosscut

there was nothing about parentid in the question when I answered it – Ileneileo

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

Shrewsbury

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.

Truditrudie

Answer 5

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.

Catacaustic

Answer 6

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.

Elation

This doesn't in fact work. As the article says, if I run DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value ) then I cannot insert new records where the old records currently are. It appears as though the only way to properly do this is to insert the data into a new table (recreating the ID's), drop the old table, and then rename the new table to the old table name. – Bentham

This will ony change the next insert seed value of the identity; not update the existing identity values. – Katrinakatrine