Select id from last inserted row [duplicate]

Question

I am using SQL Server Management Studio. I have table named faculty and its attribute are id, Name and dean. I want to get last inserted record id in this table.

eg:

id  Name  dean
1   abc   xyz
2   efg   sss
3   ert   yui

I just want to get 3rd id only, not 3rd row. Just last inserted id that is 3 in this case.

Answer 1

You can use the SCOPE_IDENTITY() in your stored procedure to get last inserted record id.

SELECT SCOPE_IDENTITY() :- It will returns the last identity value inserted into an 
identity column in the same scope.

SELECT @@IDENTITY :- @@IDENTITY will return the last identity value entered 
into a table in your current session. 

SELECT IDENT_CURRENT(‘tablename’) :- IDENT_CURRENT is not limited by scope and
session; it is limited to a specified table. 

There are other options also such as

1. Select Top 1 id From Table Order by id desc

2. SELECT max(id) FROM table

Refer msdn http://msdn.microsoft.com/en-us/library/ms190315.aspx and http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Answer 2

You can Use @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

see this Link @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT