Related Tags:
SQL SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. Learn More, SQL Server Management Studio (SSMS) SQL Server Management Studio (SSMS) is a software application first launched with Microsoft SQL Server 2005 that is used for configuring, managing, and administering all components within Microsoft SQL Server. Learn More, MSSQL Microsoft SQL Server is a relational database management system, or RDBMS, that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. It's one of the three market-leading database technologies, along with Oracle Database and IBM's DB2. Learn More, Stored Procedure A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs. Learn More,

What is the difference between/ purpose of the @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT ?

Description:I want to know the differences between or purposes of each one of the @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT() .

Posted by: | Posted on: Jul 12, 2018

1 answers



To understand the main differences between @@identity, scope_identity() and ident_current(tablename), start with the purpose of each one of them which are as follows.

-The @@identity function returns the last identity created in the same session.
-The scope_identity() function returns the last identity created in the same session and the same scope.
-The ident_current(tablename) returns the last identity created for a specific table or view in any session.

In the above definitions the session is the database connection. The scope is the current query or the current stored procedure.

Now for further understanding on scope_identity() and the @@identity functions's differences ,Lets consider if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record on an other table, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger.

Replied by: | Replied on: Jul 13, 2018