Get the last generated identity value in SQL Ident_current, @ @IDENTITY, scope_identity usage and differences

In the original: SQL gets the last generated identity value ident_current, @ @IDENTITY, scope_identity usage and differences

Ident_current

Returns the last identity value generated for the specified table in any session and any scope.

Grammar

Ident_current('table_name')

Parameters

table_name

is the name of the table whose identity value will be returned. The data type of table_name is varcharand has no default value.

return type

sql_variant

Comments

Ident_current is similar to the Microsoft®sql server™2000 identity function scope_identity and @ @IDENTITY. All three functions return the last generated identity value. However, they are different in defining the "last" scope and session.

• Ident_current returns the identity value that was last generated for any session and for a particular table in any scope.
  
• @ @IDENTITY Returns the identity value that was last generated for any table in all scopes of the current session.
  
• Scope_identity returns the identity value that was last generated for the current session and any tables in the current scope.

Example

The following example shows the different identity values returned by ident_current, @ @IDENTITY, and scope_identity.

Use Pubsdrop table T6drop table T7gocreate table T6 (ID int identity) CREATE TABLE T7 (ID int identity (100,1)) Gocreate Trigge R T6ins on T6 for insert Asbegin Insert T7 DEFAULT Valuesendgo--end of Trigger Definitionselect * from T6--id is empty . SELECT * from T7--id are empty.--do the following in Session 1INSERT T6 DEFAULT valuesselect @ @IDENTITY/*returns th E value which is inserted by the Trigger.*/select scope_identity ()/* Returns the value 1, which was inserted by T He INSERT stmt 2 statements before this query.*/select ident_current (' T7 ')/* Returns value inserted into T7, i.e. in the T Rigger.*/select ident_current (' T6 ')/* Returns value inserted into T6, which is the INSERT statement 4 Stmts before this Q uery.*/--do the following in Session 2SELECT @ @IDENTITY/* Returns NULL since there have been no INSERT action so far in th is Session.*/select scope_identity ()/* Returns NULL since there have been no INSERT action so far in this SCOPE in this SES Sion.*/select Ident_curRENT (' T7 ')/* Returns The last value inserted into t7.*/ 

具体：sql帮助文档

Get the last generated identity value in SQL Ident_current, @ @IDENTITY, scope_identity usage and differences