Primary key value after insertion of row in SQL Server 2005
Solution 1
By all means - use the SCOPE_IDENTITY()
if your ID
column is an INT IDENTITY
- only that will give you the correct results!
The first approach with the MAX(ID)
will fail terribly if you have multiple clients inserting rows almost at the same time - you'll get false results back. Don't use that!
The third approach might fail if another entry with the same values for E-Mail and name already exists.
Also, as a side-note: you should never use sp_
as your prefix! This is a Microsoft-reserved prefix and has downsides in terms of performance - use something else.
Solution 2
If you have an Identity column as primary key you should use SCOPE_IDENTITY()
You could also use the OUTPUT Clause to return the ID.
insert into tableTest(testUserEmail,testUserName)
output inserted.ID
values (@testValue, @testName)
Related videos on Youtube
Shailesh Sahu
Updated on June 04, 2022Comments
-
Shailesh Sahu almost 2 years
In SQL Server 2005 I am inserting a row into a table using a stored procedure and I want to fetch the new primary key value just after inserting that row. I am using following approach to get primary key value after insertion row
Create Proc Sp_Test @testEmail varchar(20)=null,-- Should be Unique @testName varchar(20)=null -- Should be Unique as begin insert into tableTest (testUserEmail,testUserName)values (@testValue,@testName) select MAX(ID) from tableTest --ID is Primary Key
--or
select ID from tableTest where testUserEmail =@testValue and testUserName = @testName
--or
select SCOPE_IDENTITY() as ID end
Please suggest me which approach is better to perform described task.
-
Shailesh Sahu almost 13 yearsThanks marc_s fro reply...U told "The first approach with the MAX(ID) will fail terribly if you have multiple clients inserting rows almost at the same time - you'll get false results back. Don't use that!"......But i have 1 question in my mind that if multiple user inserting row than the set of sql statements will execute separately for each clients .until procedure end block
-
marc_s almost 13 years@Shailesh Sahu: NO! If you don't have any explicit transactions (and you don't), then the
INSERT
statement in itself is an implicit transaction. Once that's run, other clients can read the values - if you have hundreds of clients inserting simultaneously, you're guaranteed to get wrong results... and even if you have a transaction - others might be readingWITH (NOLOCK)
or other mechanisms and get back false values....