CODEWITHSUNDEEP
sql-server-2008
Developer
Developer

Reputation: 8636

best way to get the last inserted record in sql server

Hi all I having a Identity column and a Computed primary key column in my table I need to get the last inserted record immediately after inserting the record in to database, So I have written the following queries can some one tell which is the best one to choose

SELECT
t.[StudentID]
FROM
[tbl_Student] t
WHERE
t.ID = IDENT_CURRENT('tbl_Student')

The other is using MAX as follows

Select 
MAX(StudentID) 
from tbl_Student

From the above two queries which is the best one to choose.

Upvotes: 3

Views: 15899

Answers (2)

Kahn
Kahn

Reputation: 1660

MAX and IDENT_CURRENT, according to technet, would behave much the same and both would be equally unreliable.

"IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL)."

Basically, to return the last insert within the current scope, regardless of any potential triggers or inserts / deletes from other sessions, you should use SCOPE_IDENTITY. Of course, that's assuming you're running the query in the same scope as the actual insert in the first place. :)

If you are, you also have the alternative of simply using OUTPUT clause to get the inserted ID values into a table variable / temporary table, and select from there.

Upvotes: 5

peter
peter

Reputation: 15119

The original answer, where my assumptions about IDENT_CURRENTwhere wrong.

Use the first one. IDENT_CURRENT should give you the last item for the current connection. If someone else would insert another student concurrently IDENT_CURRENT will give you the correct value for both clients, while MAX might give you a wrong value.

EDIT:

As it was mentioned in the other answer IDENT_CURRENTand MAXare equally unreliable in case of concurrent usage. I would still go for IDENT_CURRENT but if you want to get the last identity used by the current scope or session you can use the functions @@IDENTITY and SCOPE_IDENTITY. This technet article explains the detailed differences between IDENT_CURRENT, @@IDENTITY and SCOPE_IDENTITY.

Upvotes: 2

Related Questions