Reassigning identifiers to a non-IDENTITY type field in a SQL Server database

Question

WARNING: This grief story contains examples of code smells and poor design decisions, as well as technical debt.

If you are familiar with SOLID principles, use TDD and unit test your work, DO NOT READ . If you do not want you to praise someone for misfortune and gloating in your own astonishment, knowing that you will never leave such a monumental pile of crap for your successors.

So, if you are sitting comfortably, I will begin.

In this application, which I inherited and supported and corrected errors over the past 7 months, I stayed with DOOZY balls from the developer, who left 6 and a half months ago. Yes, 2 weeks after I started.

Anyway. This application has tables clients , employees and visits .

There is also a table called AppNewRef (or something similar) that ... is waiting for it ... contains the following record identifier, which will be used for each of the other tables. Therefore, they may contain data such as: -

 TypeID Description NextRef 1 Employees 804 2 Clients 1708 3 Visits 56783 

When an application creates new rows for employees , it looks in the AppNewRef table, gets the value, uses that value for the identifier, and then updates the NextRef column. The same goes for clients and visits and all other tables for which NextID is stored here.

Yes, I know there are no IDENTITY columns in this database automatically. All under the pretext of "when it was an Access application." These identifiers are stored in code (VB6) as long. Thus, up to 2 billion 147 million records are possible. Ok, this works pretty well. (except that the application is being updated and takes care of locking / updating, etc., and not in the database)

So, our users are pretty happily creating employees , clients , visits , etc. Visits ID constantly growing dozens at a time. Then there are problems. Our clients cause the database to crash when creating visit packages because the server is working fine and the application becomes unresponsive. Therefore, they kill the application using the task manager instead of being patient and waiting. Of course, the application seems to be blocking.

Go to earlier this year, and developer Tim (real name, not protecting the perpetrators here) begins to modify the code to perform batch updates in stages, so that the user interface remains "responsive." Then comes April, and he is working on his notification (you can imagine the scene now, right?), And he left to finish the updates.

In late April and early May, we update some of our customers. Over the next few months we are updating more and more.

Without knowing Tim (real name, remember) and me (who started two weeks before Tim left) and another new developer who started a week later, the identifier in the visit table starts making huge jumps up. By the huge, I mean 10,000, 20,000, 30,000 at a time. Sometimes a few hundred thousand.

Here is a graph illustrating the rapid increase in identifiers used.

Roll in November. Technical support service and reports that it receives an error message. I am looking at an error message and requesting a database so that I can debug the code. I find that the value is too great for a long time. I make some queries, extract information, drop it in Excel and draw.

I don’t think that making the code longer than the long one for the identifier is the right approach, since this application passes this identifier to other DLLs and OCX and breaks the interface on those that just seem like a whole world that I don’t want to meet right now.

One potential idea I'm exploring is to try changing the identifier so that I can bring them to a lower level. Essentially filling in the blanks. Using the ROW_NUMBER

What I'm going to do is add a new column to each of the tables that have a foreign key reference for these visit IDs (which are not a valid foreign key, these restrictions do not exist in this database). This new column can store the old (current) value of the visit identifier (oh, just to confuse things; in some tables it is called EventID , and on some called VisitID ).

Then, for each of the other tables related to this VisitID , update the new value.

Ideas? Suggestions? T-SQL snippets to help everyone with thanks.

Answer 1

Option one:

Explicitly limit all of your relationships with a foreign key and set them as ON UPDATE CASCADE .

This means that whenever you change the identifier, foreign keys will be automatically updated.

Then you just run something like this ...

 WITH resequenced AS ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS newID, * FROM yourTable ) UPDATE resequenced SET id = newID 

I did not do this at the age of , so I forget that this causes problems in the middle of the update, having two entries with the same id value. If so, you can do something like this first ...

 UPDATE yourTable SET id = -id 

Option Two:

Be sure to explicitly indicate none of your foreign keys. If they are, pay attention to them and delete them.

Then do something like ...

 CREATE TABLE temp AS newID INT IDENTITY (1,1), oldID INT ) INSERT INTO temp (oldID) SELECT id FROM yourTable /* Do this once for the table you are re-identifiering */ /* Repeat this for all fact tables holding that ID as a foreign key */ UPDATE factTable SET foreignID = temp.newID FROM temp WHERE foreignID = temp.oldID 

Then reapply any existing foreign key relationships.

This is a pretty scary option. If you forget to update the table, you just bite your data. But you can give this temp table a much nicer name and STORE it.

Good luck. And may the lord have mercy on your soul. And Tim, if you meet him in a dark alley.

Answer 2

I would create a numbers table that only has a sequence from 1 to any maximum in increments of 1 for a long time, and then change the logic for getting maxid for visitid, and maybe others make the right connection between numbers and the visit table. and then you can just look for te min of this number

 select min(number) from visits right join numbers on visits.id = numbers.number 

This way you get all the filled spaces without changing any of the other tables.

but I would just redo the whole database.