SQL Server 2019 : select just inserted row-CodePudding

I have a table with id set as primary_key/is_identity, so I was wondering if I can assume the correctness in finding the row just inserted using SELECT TOP 1 in the middle of BEGIN TRANSACTION and COMMIT TRANSACTION by using a reverse order:

BEGIN TRANSACTION

INSERT INTO [MyTable] ([description], [name])
VALUES ('animal', 'cat')

SELECT TOP (1) [id], [description], [name]
FROM [MyTable]
ORDER BY [id] DESC

COMMIT TRANSACTION

Is that always correct, considering the mentioned constraints and the transaction instructions?

CodePudding user response：

No, you cannot rely on TOP (1) to get the row you inserted, because someone else could have inserted a row after you did but before your select, yes even inside a transaction. In high concurrency this is going to lead to tears, unless you make your transaction serializable. But that is unnecessary, since you can always get the row(s) you inserted using the inserted pseudo-table:

INSERT INTO dbo.MyTable ([description], [name])
OUTPUT inserted.id, inserted.description, inserted.name
VALUES ('animal', 'cat');

Example db<>fiddle

For single-row inserts, yes, you can use SCOPE_IDENTITY(), but OUTPUT works for both single-row and multi-row inserts.

CodePudding user response：

No, this is NOT safe - if multiple inserts happen at the same time, just selecting the TOP 1 could give you a wrong result!

You need to rely on the ID that was just inserted:

BEGIN TRANSACTION

INSERT INTO [MyTable] ([description], [name])
VALUES ('animal', 'cat')

-- this fetches the IDENTITY "ID" of your row having been inserted
DECLARE @NewID INT = SCOPE_IDENTITY();

-- get that freshly inserted row of your context/scope
SELECT [id], [description], [name]
FROM [MyTable]
WHERE id = @NewID;

COMMIT TRANSACTION