Incrementing a value with a set based solution
-
Kenneth is absolutely correct... if you're not going to use IDENTITY, then you really must have a counter/sequence table and a stored proc to get the nextid. Repost if you need to know how to make one out of the code below but, here's a hint to prevent deadlocking on the table... use the SQL Server Proprietary Update method to update the table and return the ID from the table instead of starting a transaction, selecting, and updating... DO NOT EMBED THIS IN A TRANSACTION!!!
- UPDATE sequence_table_name
SET
@nextid_variable = sequenceid_column = sequenceid_column + @increment
FROM sequence_table_name
WHERE TableName = @tableidentifier_variable
How do I know this? It resolved an average of 620 deadlocks per day on a 3rd party "solution"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - UPDATE sequence_table_name
-
Oh CRUD! No, you don't even want to think of building a sequence table for this... here's why...
You said that the 3rd party stuff
creates new ID's without the use of a sequence table and the column is not an IDENTITY column... that means that they must be using some other method (could be a MAX thing or maybe getting something from SYSINDEXES). Whatever the case, if you create a sequence table, it will surely get out of sync when the 3rd party software inserts new records. So, I'm thinking you're stuck with the MAX method... many will die....
...Unless... you make sure the target ID column has a unique constraint and you write your MAX method to "try again" if there's a constraint error.
Personally, I'd get a hold of the 3rd party providers (preferably, around the neck
) and make them fix the garbage they wrote because this is a bit insane and you'll need to constantly create miserable work arounds for the the life of the software.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply