Monday, August 15, 2011

Last id value in a table. SQL Server

Posted by Rahul Kharde at 11:40 PM
In this article I would like to share my idea about getting Identity after a row was inserted in to the SQL Server 2005. After inserting a row into the database which has primary key field, most of the time we need the identity, we have three approaches based on our requirements and situations.

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

SELECT @@IDENTITY
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
SCOPE_IDENTITY() will return the last IDENTITY value produced on a connection and by a statement in the same scope, without based on the table that produced the value. So we can say that this function is some identical to @@IDENTITY with one exception. like @@IDENTITY will return the last identity value created in the current session, but it will also limit it to your current scope as well . So that means it will return identity value inserted in Table.

SELECT IDENT_CURRENT(‘tablename’)
IDENT_CURRENT will reutrn returns the last IDENTITY value produced in a table, Without based on the connection that created the value, and Without based on the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session., So it will retrieve the last generated table identity value.

Example

CREATE TABLE Student
(
    sid INT identity PRIMARY KEY
   ,Name Varchar(255) NOT NULL
 )


INSERT INTO Student(Name)VALUES('abc')
INSERT INTO Student(Name)VALUES('pqr')
INSERT INTO Student(Name)VALUES('xyz')
-- will return the last identity value entered into a table
SELECT @@Identity
-- will return max value into the table
SELECT max(sid) FROM Student
-- will reutrn returns the last IDENTITY value produced in a table
SELECT IDENT_CURRENT('Student')
-- will return TOP 1 id and ORDER BY DESC
SELECT TOP 1 sid FROM Student ORDER BY sid DESC
-- will return the last IDENTITY value produced on a connection and by a statement in the same scope
SELECT SCOPE_IDENTITY()





If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top