In SQL Server, if you ever need to return the value created in an identity column, you have several options. Each of these options, although similar, do a slightly different thing.
Tag: functions
Use SCOPE_IDENTITY() to Return the Last-Inserted Identity Value in the Same Scope (SQL Server)
In SQL Server, you can use the T-SQL SCOPE_IDENTITY() function to return the last identity value inserted into an identity column in the same scope.
A scope is a module (stored procedure, trigger, function, or batch). If two statements are in the same stored procedure, function, or batch, they are in the same scope.
Note that it returns the last identity value generated in any table in the current session. This is in contrast to the IDENT_CURRENT() function, which returns the last-inserted identity value for a given table, regardless of which session it’s in.
SCOPE_IDENTITY() is very similar to @@IDENTITY in that they both return the last-inserted identity value in the current session. The difference is that SCOPE_IDENTITY() is limited to the current scope, whereas @@IDENTITY is not limited to a specific scope.
Use @@IDENTITY to Return the Last-Inserted Identity Value in SQL Server
In SQL Server, you can use the T-SQL @@IDENTITY system function to return the last-inserted identity value in the current session.
Note that it returns the last identity value generated in any table in the current session. This is in contrast to the IDENT_CURRENT() function, which returns the last-inserted identity value for a given table.
The SCOPE_IDENTITY() function is very similar to @@IDENTITY in that it also returns the last-inserted identity value in the current session. The difference is that SCOPE_IDENTITY() is limited to the current scope.
Use IDENT_CURRENT() to Return the Current Identity Value on an Identity Column in SQL Server
In SQL Server, you can use the T-SQL IDENT_CURRENT() function to return the last identity value generated for a specified table or view on an identity column. The last identity value generated can be for any session and any scope.
IDENTITY() vs IDENTITY() in SQL Server: What’s the Difference?
It might seem strange, but T-SQL has an IDENTITY() property and an IDENTITY() function, each of which serve a (similar, but) different purpose.
- The
IDENTITY()property creates an identity column in a table. An identity column contains an automatically incrementing identification number. This property is used with theCREATE TABLEandALTER TABLEstatements. - The
IDENTITY()function however, is used only in aSELECTstatement with anINTOtable clause to insert an identity column into a new table.
There’s also the SQL-DMO Identity property that exposes the row identity property of a column, but I won’t cover that here. Microsoft advises that this property will be removed in a future version of SQL Server, and that you should avoid using it in new development work.
How to Use the IDENTITY() Function in SQL Server
In SQL Server, you can use the IDENTITY() function to insert an identity column into a new table.
However, this function is not to be confused with the IDENTITY() property, which is used with the CREATE TABLE and ALTER TABLE statements.
The IDENTITY() function is used only in a SELECT statement with an INTO table clause. So you can use it when transferring data from one table to another, for example.
How SESSION_CONTEXT() Works in SQL Server
In SQL Server, you can use the SESSION_CONTEXT() function to read the value of a specified key in the current session context.
The key (key/value pair) needs to be set first. This can be done with the sp_set_session_context stored procedure.
Once a key/value pair has been set for the session, you can use SESSION_CONTEXT() to return that key’s value.
NEWID() vs NEWSEQUENTIALID() in SQL Server: What’s the Difference?
In SQL Server, both the NEWSEQUENTIALID() function and the NEWID() function create a GUID (Globally Unique IDentifier), also known as UUID (Universally Unique IDentifier).
A GUID can be used as a unique identifier in columns of type uniqueidentifier, so both functions can be used for that purpose.
However, there are differences between these two functions that may influence your decision to use one over the other.
Use NEWSEQUENTIALID() to Create an Incrementing GUID in SQL Server
In SQL Server, you can use the NEWSEQUENTIALID() function to create incremental unique values.
It creates a GUID (Globally Unique IDentifier) that is greater than any GUID previously generated by this function on a specified computer since the operating system was started. After restarting the operating system, the GUID can start again from a lower range, but is still globally unique.
The NEWSEQUENTIALID() function can only be used with DEFAULT constraints on table columns of type uniqueidentifier. Therefore, you can’t just run a query like SELECT NEWSEQUENTIALID() and expect it to work (but you can do that with the NEWID() function).
Use NEWID() to Create a Unique Value in SQL Server
In SQL Server, you can use the NEWID() function to create a unique value.
More specifically, it’s an RFC4122-compliant function that creates a unique value of type uniqueidentifier.
The value that NEWID() produces is a randomly generated 16-byte GUID (Globally Unique IDentifier). This is also known as a UUID (Universally Unique IDentifier).