0

I have a table with something around 300 records in SQL Server 2017 Express. Now the issue is that some of the records got the ID shifted by a 1000. I need to update the records to discount the 1000 added.

The problem is that (wrongly, I fully agree) this ID column is being used by the user of a system to identify orders. In that case, since this value is presented and used by the end user, they do not accept the skip. I fully agree it was a very wrong design when the system was created, but right now I need to correct the values.

So I would like to do something like:

Update table SET Id = Id - 1000 WHERE Id > 1000;

The issue is that the column is identity and SQL Server does not allow it to be updated.

I can set the value on insert via SET IDENTITY_INSERT table ON but I cannot change.

So how can I correct the IDs if the update is blocked?

I've tried creating a column IdTemp, copying the values to it and update it. Then I considered dropping the Id column and making IdTemp identity, but I don't see how to turn a new column to identity.

So how can I proceed?

R.J. Dunnill
  • 2,049
  • 3
  • 10
  • 21
user1620696
  • 10,825
  • 13
  • 60
  • 81
  • 3
    Seems to me you are putting way to much emphasis on the value of your identity. This skip has been well known and documented since sql server 2005. Remember that an identity is essentially a random number that has no significance. And gaps are perfectly normal an expected. Consider what happens when you delete a row. – Sean Lange Jul 08 '19 at 16:32
  • @SeanLange I agree with you. I know to correct so no more skip happens. The issue is that the user of the system uses the Id value to identify orders and simply does not accept the skip. I do agree it was an error of design of the time the system was created (to let this Id be the identifier of the orders), but now I must find a way to deal with it. – user1620696 Jul 08 '19 at 16:34
  • An additional column should be added to the table for TicketId instead of using the **internal** database identifier – devlin carnate Jul 08 '19 at 16:52
  • The easiest way IMO would be to do an `INSERT` with `SET IDENTITY_INSERT` on to get the rows inserted with the desired values. Then delete the unwanted ones and reseed the identity. But nothing will stop this happening again – Martin Smith Jul 08 '19 at 16:53
  • Hi @MartinSmith, thanks for the suggestion. I did one `INSERT` selecting the values from the same table with the condition `id > 1000` and already discounting the 1000 in the select statement. It worked really nice. Thanks. To prevent it occuring again I believe the best option would be to create a separate column for the public identifier which is incremented in the application before saving and copying the already existing data there, am I right? – user1620696 Jul 08 '19 at 17:20
  • Stop and think first. What happens when some row in this table is deleted. Now you have a gap. What should happen? In addition, your previous comments implied that you could not change the design/code of the system. Now apparently you propose to manage the ID generation process from within the app - which is a redesign! So - what does "best" mean here? Is the actual value stored with the row important here? Or is this simply an issue with display of these rows using a sequential number? And before you do anything - is this the primary key of the table? – SMor Jul 08 '19 at 17:24

1 Answers1

2

Be lazy:

  1. Open SSMS, right-click on the table, click Design. Select the column with the identity, then in column properties, set Identity Specification (Is Identity) to No. Then from the Table Designer menu, select Generate Change Script.

  2. Make sure everyone is out of the database.

  3. Run the script.

  4. Update your values.

  5. Do the reverse of step 1.

Stu
  • 15,675
  • 4
  • 43
  • 74
  • Hi @Stu, I've considered that, but when I try to set the identity to no, it says that this would required the table to be recreated and doesn't allow it. – user1620696 Jul 08 '19 at 17:18
  • 3
    Prepare to laugh and cry: in SSMS, go to Tools -> Options -> Designers -> Table and Database Designers, and uncheck "Prevent saving changes that require table re-creation". – Stu Jul 08 '19 at 20:43