Inherited SQL Server Database Problem

Question

I've inherited a SQL Server Database that for some odd reason the developers didn't use Identity to auto increment the primary key. (maybe it was once an Oracle one, who knows).

Now,... since the database is accessed from countless clients, I presume that the "next ID" must be stored in the DB somewhere so that they don't conflict with each other. If I attempt to add the record by hand it works, but then when the client creates a record it fails saying the key is already in use.

How can I determine what table it's storing it in, or can you suggest somewhere else that the ID could be stored?

It might help that I know the next ID to be generated. since there might be a way of finding a value in any record in any table. (If not, there should be :)

Answer 1

check information_schema.constraint_column_usage for a table that is referenced by all the others with a foreign key.

Answer 2

It could be being stored in code, or even worse, determined every time in code. e.g. Before inserting, they grab the highest current ID, then increment by 1 and insert.

Answer 3

I've seen a couple of these systems.

• One had a table with an auto-number that would be used to set the identity on each table. This was used to ensure that each object had a different id.

• The other project the was given by something like: select max(id)+1 from table. Don't ask me why ;)

Answer 4

If it's using stored procs for inserting then it's probably in there.

Answer 5

After you get things settled and working again, switch to Uniqueidentifier (GUID). Not only can they be generated in independent time and space eliminating race conditions, but you can use them as a ROWGUIDCOL for replication purposes.

Answer 6

Your best bet to find it is to look at the code (either a stored proc or code executed from the GUI dynamically) that inserts a record. Too often systems "designed" this way do not work properly and do not account for mulitple users asking for the information simultaneoulsy. likely the failure at the client level is precisely because they are running into race conditions.

If you do change it to use Identity (and frankly if that will work, I'd rather do that than use GUIDS) and you need to get that value back for inserting into child tables, then make sure you use scope_identity() to return the identity value you just inserted and not @@identity.

Answer 7

How about if you ran an SQL Server Profiler session (assuming you're using SQL Server 2005+)? This tool will show the queries being executed by the application. If the next ID really is being stored in a table within the database, you should see a query accessing it either at startup or immediately before the record is inserted. You should also see the next ID being updated after an insertion. If you don't see any of that, maybe you'll still get some valuable clues.