SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

@@Identity, Scope_Identity() , Ident_Current(‘table_name’) : Get last inserted record in a table – Sql Server

on March 4, 2013

Select @@Identity

Gets the identity value entered into to the table in your current session.Scope of @@identity is not limited. If there is a trigger which would create an entry in another table, you will get the identity created in the last.

in other words:

It returns the last identity value generated for any table in the current session, across all scopes.

Select Scope_Identity()

It returns the last identity value generated for any table in the current session and the current scope.

Hence, if we consider the example given above the scope_identity would give the identity value generated by explicit insertion rather than the trigger.

Select Ident_Current(‘table_name’) 

It returns the last identity value generated for a specific table in any session and any scope.

Scope_Identity equivalent for GUID:

http://stackoverflow.com/questions/1509947/sql-server-scope-identity-for-guids

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: