So I need to implement essentially an auto-increment column. I have a column in one table that has an int that will act as the counter, and there will be another table with a corresponding number of rows, each row having it's own counter. I thought this would be fairly simple to implement, but after doing some research I found a few forums where they mention there could be issues in situations like this if multiple transactions were running roughly at the same time, even by running the select,update, & insert in one transaction
After looking into though it seemed to me that if I used the OUTPUT clause to grab the new value the first table is getting updated with, I could avoid that issue entirely.
So this is the T-SQL I'm thinking of. Should this be okay for my situation?
BEGIN TRANSACTION;
DECLARE @tblCtrs table (Ctr int)
DECLARE @CtrVal INT
UPDATE Table1
SET Ctr = Ctr+1
OUTPUT inserted.Ctr INTO @CtrVals
WHERE id=<whatever id value>
SET @CtrVal = (SELECT TOP(1) Ctr FROM @tblCtrs)
INSERT INTO TABLE2 (Ctr, ....)
VALUES( @CtrVal, ....)
COMMIT;
If I understand your question correctly, the concern is that concurrent transactions could cause unintended results with the counter value. You know how to manage the counter values just fine, it's just a matter of whether or not some other transaction could read/modify at same time. If that understanding is correct, that's what isolation levels are for. Depending upon how the data is accessed/stored (e.g. can there be multiple records with the same <whatever id value> or is that unique?) and application requirements (e.g. is it OK if <whatever id value> is not unique and there is insert of new record that is not updated due to timing) you should SET TRANSACTION ISOLATION LEVEL accordingly.