SQL Server INSERT, Scope_Identity() and physical writing to disc
- by TheBlueSky
Hello everyone,
I have a stored procedure that does, among other stuff, some inserts in different table inside a loop. See the example below for clearer understanding:
INSERT INTO T1 VALUES ('something')
SET @MyID = Scope_Identity()
... some stuff go here
INSERT INTO T2 VALUES (@MyID, 'something else')
... The rest of the procedure
These two tables (T1 and T2) have an IDENTITY(1, 1) column in each one of them, let's call them ID1 and ID2; however, after running the procedure in our production database (very busy database) and having more than 6250 records in each table, I have noticed one incident where ID1 does not match ID2! Although normally for each record inserted in T1, there is record inserted in T2 and the identity column in both is incremented consistently.
The "wrong" records were something like that:
ID1 Col1
---- ---------
4709 data-4709
4710 data-4710
ID2 ID1 Col1
---- ---- ---------
4709 4710 data-4709
4710 4709 data-4710
Note the "inverted", ID1 in the second table.
Knowing not that much about SQL Server underneath operations, I have put the following "theory", maybe someone can correct me on this.
What I think is that because the loop is faster than physically writing to the table, and/or maybe some other thing delayed the writing process, the records were buffered. When it comes the time to write them, they were wrote in no particular order.
Is that even possible if no, how to explain the above mentioned scenario?
If yes, then I have another question to rise. What if the first insert (from the code above) got delayed? Doesn't that mean I won't get the correct IDENTITY to insert into the second table? If the answer of this is also yes, what can I do to insure the insertion in the two tables will happen in sequence with the correct IDENTITY?
I appreciate any comment and information that help me understand this.
Thanks in advance.
