Skip to main content

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

 SELECT @@IDENTITY

It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@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()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), 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. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure

Comments

Post a Comment

Popular posts from this blog

Methods of Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

SQL Server provides us with a number of window functions that help us to perform calculations across a set of rows, without the need to repeat the calls to the database. Unlike the standard aggregate functions, the window functions will not group the rows into a single output row, they will return a single aggregated value for each row, keeping the separate identities for those rows. The Window term here is not related to the Microsoft Windows operating system, it describes the set of rows that the function will process. One of the most useful types of window functions is Ranking Window Functions that are used to rank specific field values and categorize them according to the rank of each row, resulting in a single aggregated value for each participated row. There are four ranking window functions supported in SQL Server;  ROW_NUMBER(),   RANK() ,  DENSE_RANK()  and  NTILE() . All these functions are used to calculate ROWID for the provided rows window in ...
Post a Comment
Read more
Q- What is DBCC command in SQL sever. A - DBCC  (Database consistency checker ) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises. I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples. These are four types: Maintenance Informational Validation Miscellaneous                Maintenance Commands Performs maintenance tasks on a database, index, or file group. Syntax: DBCC  CLEANTABLE ( ‘DatabaseName’,’TableName.ColumnName’ ,0) 2.  DBREINDEX  – Builds one or more indexes for the table in the specified database. (Will be  removed  in the future version, use  ALTER INDEX  instead) Syntax: USE DatabaseName DBCC  DBREINDEX ( ‘TableName.ColumnName’,’PK_ID’ ,80) 3.  DROPCLEANBUFFERS  – Removes all clean buffers from buffer pool. Synta...
1 comment
Read more

Write a query that prints a list of employee names for employees in Employee having a salary greater than 2000

  Write a query that prints a list of employee names (i.e.: the  name  attribute) for employees in  Employee  having a salary greater than  2000  per month who have been employees for less than  10  months. Sort your result by ascending  employee_id . Input Format The  Employee  table containing employee data for a company is described as follows: Column Type employee_id Integer name String months Integer salary Integer where  employee_id  is an employee’s ID number,  name  is their name,  months  is the total number of months they’ve been working for the company, and  salary  is the their monthly salary. Sample Input employee_id name months salary 12228 Rose 15 1968 33645 Angela 1 3443 45692 Frank 17 1608 56118 Patrick 7 1345 59725 Lisa 11 2330 74197 Kimberly 16 4372 78454 Bonnie 8 1771 83565 Michael 6 2017 98607 Todd 5 3396 99989 Joe 9 3573 Sample Output Angela Michael Todd Joe Explanat...
1 comment
Read more