Obtain the last generated id value IDENT_CURRENT, @ IDENTITY, SCOPE_IDENTITY in SQL

Source: Internet
Author: User

IDENT_CURRENT
Returns the final id value generated for any session and specified table in any scope.

Syntax
IDENT_CURRENT ('table _ name ')

Parameters
Table_name

Is the name of the table whose ID value will be returned. The data type of table_name is varchar, with no default value.

Return type
SQL _variant

Note
IDENT_CURRENT is similar to the SCOPE_IDENTITY and @ IDENTITY functions of Microsoft SQL Server 2000. All three functions return the last generated id value. However, they have different scopes in defining "last" and sessions.

IDENT_CURRENT returns the last generated id value for any session and specific tables in any scope.

@ IDENTITY returns the last generated id value for any table in all scopes of the current session.

SCOPE_IDENTITY returns the last generated id value for the current session and any table in the current scope.
Example
The following example shows the different ID values returned by IDENT_CURRENT, @ IDENTITY, and SCOPE_IDENTITY.

USE pubs
Drop table t6
Drop table t7
GO
Create table t6 (id int IDENTITY)
Create table t7 (id int IDENTITY (100,1 ))
GO
Create trigger t6ins ON t6 FOR INSERT
AS
BEGIN
INSERT t7 DEFAULT VALUES
END
GO
-- End of trigger definition

SELECT * FROM t6
-- Id is empty.

SELECT * FROM t7
-- Id is empty.

-- Do the following in Session 1
INSERT t6 DEFAULT VALUES
SELECT @ IDENTITY
/* Returns the value 100, which was inserted by the trigger .*/

SELECT SCOPE_IDENTITY ()
/* Returns the value 1, which was inserted by
INSERT stmt 2 statements before this query .*/

SELECT IDENT_CURRENT ('t7 ')
/* Returns value inserted into t7, I. e. in the trigger .*/

SELECT IDENT_CURRENT ('t6 ')
/* Returns value inserted into t6, which was the INSERT statement 4 into ts before this query .*/

-- Do the following in Session 2
SELECT @ IDENTITY
/* Returns NULL since there has been no INSERT action
So far in this session .*/

SELECT SCOPE_IDENTITY ()
/* Returns NULL since there has been no INSERT action
So far in this scope in this session .*/

SELECT IDENT_CURRENT ('t7 ')
/* Returns the last value inserted into t7 .*/
Specific: SQL help documentation

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/jinho/archive/2010/01/28/5267507.aspx

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.