In SQL Server, the IDENTITY property is used to automatically generate unique values for a column in a table. This is commonly used for creating primary keys or other unique identifiers. When you insert a new row into a table with an IDENTITY column, SQL Server automatically assigns a unique value to that column.

We have a few useful Identity functions in SQL Server to work with the IDENTITY columns in a table. Let’s explore the following IDENTITY functions with examples.

  • SQL @@IDENTITY Function
  • SQL SCOPE_IDENTITY() Function
  • SQL IDENT_CURRENT Function
  1. @@IDENTITY Function:
  • @@IDENTITY is a system function that returns the last identity value generated in the current session for any table with an identity column.
  • It returns the value as a numeric data type.
  • In SQL Server, an identity column is a powerful feature used to automatically generate incremental and unique values for a specific column within a table. These values are often employed as primary keys or unique identifiers for records. When creating an identity column, you have the flexibility to define two essential attributes: the seed value and the increment value.
  • The seed value, also known as the initial value, represents the starting point for the auto-generated values. In your example, you mentioned that you want to start with an EmployeeID of 100. This means that the seed value for the identity column would be set to 100. This initial value serves as the baseline from which SQL Server begins generating subsequent values.

 

SELECT @@IDENTITY AS LastIdentity;

2. SCOPE_IDENTITY() Function:

  • SCOPE_IDENTITY() is a function that returns the last identity value generated within the current scope for any table with an identity column.
  • It is more reliable than @@IDENTITY because it specifically returns the identity value generated within the current scope.
  • The SCOPE_IDENTITY() function in SQL is used to retrieve the most recently generated IDENTITY value within the current scope. This scope can be within a module, trigger, function, or stored procedure. It is similar to the @@IDENTITY function but is limited to the current scope, ensuring that you get the identity value specifically generated in that scope.
  • If you attempt to use SCOPE_IDENTITY() before an INSERT statement generates an IDENTITY value in the same scope, it will return NULL.

 

 

 

 

Let’s insert value in the Employee data table and view the output of both @@IDENTITY and SCOPE_IDENTITY() functions. In the current session, we inserted data into the EmployeeData table. It generates an identity in this table. The identity seed value is 1 for the EmployeeData table.Once we insert value in the EmployeeData table, it fires a trigger to insert value in the Departments table. The identity seed value is 100 for the Departments table.

  • We get the output 100 for the SELECT @@IDENTITY function
  • SCOPE_IDENTITY function returns identity value under the current scope only. It gives output 1 for this function

IDENT_CURRENT Function:

  • IDENT_CURRENT is a function that returns the last identity value generated for a specified table and column, regardless of the session or scope.
  • You specify the table name and column name as parameters to this function.
  • The IDENT_CURRENT function in SQL Server is a useful tool for obtaining the last identity value generated for a specified table across different connections or scopes. It disregards the current scope and retrieves the identity value for the specified table, irrespective of where or how that value was generated.
  • In your provided scenario, you’ve demonstrated how IDENT_CURRENT can be used to check the identity value in a different connection window. This function is not limited by scope or session, making it a valuable tool for maintaining consistency in tracking identity values across various parts of your application.
  • For example, let’s say you have an Department table with an identity column for id and you insert data into it from Session ID 53, generating an identity value of 10028 . Even if you check the current identity value for the Department table from a different connection or session, you will still receive the same output, which, in this case, is identity value 10028. This demonstrates that IDENT_CURRENT provides a consistent way to access the last identity value generated for a specific table, regardless of the context in which the insert occurred.This functionality is particularly helpful in scenarios where you have multiple concurrent users or processes interacting with the same database. It ensures that you can reliably retrieve the latest identity value for auditing, reporting, or referencing purposes, regardless of the connection or scope from which you inquire about it.

 

SELECT IDENT_CURRENT(‘Employee’AS LastIdentity;

 

Click_here _to_knowmore

Leave a Reply

Your email address will not be published. Required fields are marked *