SQL Server Identity Column Renumbering

Question

I have a table which has a primary key ID column and is set to increment 1 for each new entry, a fairly standard configuration. This table is updated regularly with new records, and is archived once a month to an archive table and then truncated.

Under normal operation the next record to go into the table after truncation would be whatever the next ID value is, ensuring that there is no overlap in IDs between the archive table and the "live" table.

Due to unknown factors, somehow the ID column in the live table "reverted" back to an ID that has already been used. Because this database was designed poorly, now I've got some data integrity issues.

My solution to resolve the issue is to update the ID column for all records in the "live" table so there will be no overlap with the ID's that exist in the "archive" table. Once I update the IDs I will reseed the Identity column so that future records will also not overlap.

My question is not about reseeding the column (using DBCC -- I know how to do this) but about how to go about re-numbering the existing records with new ID values. Should I do this with a looped dynamic SQL query (delete record, insert record), or is there some SQL Server function I am unaware of that can automatically renumber the ID column?

Don't know why you need looping or dynamic SQL. `BEGIN TRANSACTION; DECLARE @i INT; SELECT @i = MAX(id) FROM dbo.table WITH (HOLDLOCK); INSERT dbo.table(othercols) SELECT othercols FROM dbo.table; DELETE dbo.table WHERE id <= @i; COMMIT TRANSACTION;` — Aaron Bertrand, Jun 30 '15 at 20:19
This is the type of answer I was looking for, thanks! I know it didn't need dyn SQL necessarily, just couldn't think of a smart way of doing this. If you put this in an answer instead of a comment I will mark it 'accepted.' — LegendaryDude, Jun 30 '15 at 20:41

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

This looks to be a duplicate of this question - How to update Identity Column in SQL Server?

The upshot of the conversations on there is that it is possible to do this but not advisable - especially if there are any other tables that refer to this identifier value in foreign keys etc. Better to cycle through and recreate the records using a cursor or similar, as you mention in your question.

edited May 23 '17 at 11:43

Community

1
1

answered Jun 30 '15 at 20:22

John Griffiths

68
7

If it's a duplicate, then *flag* as a duplicate. – LittleBobbyTables - Au Revoir Jul 01 '15 at 12:24
I can't because that capability is only available for users with higher reps. The only way I could respond was to post an answer. http://meta.stackexchange.com/questions/217242/why-dont-i-see-the-option-to-mark-a-question-as-a-duplicate – John Griffiths Jul 01 '15 at 16:41
Huh, I never realized that flagging (at 15 reputation) was different than flagging as a duplicate (at 50 reputation). My apologies! – LittleBobbyTables - Au Revoir Jul 01 '15 at 17:54
No worries. It's a pretty silly rule. – John Griffiths Jul 01 '15 at 21:09

Philip Devine · Answer 2 · 2015-06-30T21:02:30.943

0

Just a simple update with a CTE should do it. If you want the ID's to start at a certain place, that's what the offset is for in the comment.

with myCTE as (
 select row_number() over (order by auditDate) + 0 rn/*This is whatever you want the ID's to start at*/, * from testTable 
)
update myCTE set id= rn;

Here's the SQL fiddle (I used a really simple table from another example I had up, but it should be easy to see what's going on)

http://sqlfiddle.com/#!3/ef0b36/1

edited Jun 30 '15 at 21:02

answered Jun 30 '15 at 20:24

Philip Devine

1,169
5
11

why are you setting dbsize to 0 just before updating it ? Waste of writes. You cant use common table expressions in sqlserver 2005 – t-clausen.dk Jun 30 '15 at 20:26
it was bad test table names. I've updated it in my answer (the sql fiddle remains the same though). I'm setting all of the ID's to 0. – Philip Devine Jun 30 '15 at 20:28
@t-clausen.dk perhaps you are thinking 2000? CTEs work fine in SQL Server 2005. – Aaron Bertrand Jun 30 '15 at 20:55
@t-clausen.dk good point on the update. bad point on the CTE. – Philip Devine Jun 30 '15 at 21:03
1

Not just waste of writes, but you can't do that (`Msg 8102, Level 16, State 1 / Cannot update identity column 'id'.`) – Aaron Bertrand Jun 30 '15 at 21:32
It was assumed based on another comment by OP that he was aware of how to turn on the identity insert. I wasn't going to be pedantic but I can see how it might be misleading. – Philip Devine Jun 30 '15 at 21:34
1

`IDENTITY_INSERT` would turn on identity **insert**, not identity **update**. – Aaron Bertrand Jun 30 '15 at 21:40
Absolutely right, you can basically disregard everything in my answer :) Can't say I've ever had to update an identity column so it's never even occurred to me. – Philip Devine Jun 30 '15 at 21:43
@PhilipDevine If your answer is wrong then you should delete it to prevent anyone from running it. – Hashim Aziz Apr 26 '23 at 18:46

SQL Server Identity Column Renumbering

2 Answers2