Tag: t-sql

Use SCOPE_IDENTITY() to Return the Last-Inserted Identity Value in the Same Scope (SQL Server)

Posted on by Ian

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.

Continue reading →

Use @@IDENTITY to Return the Last-Inserted Identity Value in SQL Server

Posted on by Ian

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.

Continue reading →

IDENTITY() vs IDENTITY() in SQL Server: What’s the Difference?

Posted on by Ian

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 the CREATE TABLE and ALTER TABLE statements.
  • The IDENTITY() function however, is used only in a SELECT statement with an INTO table 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.

Continue reading →

How to Use the IDENTITY() Function in SQL Server

Posted on by Ian

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.

Continue reading →

How SESSION_CONTEXT() Works in SQL Server

Posted on by Ian

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.

Continue reading →

Set Key/Value Pairs in the Session Context in SQL Server (sp_set_session_context)

Posted on by Ian

In SQL Server, you can use the sp_set_session_context stored procedure to set key/value pairs in the session context.

These key/value pairs then remain in your session context until your connection to SQL Server closes. You can use these values in stored procedures and other T-SQL code throughout the life of your session.

This method for maintaining session state was first introduced in SQL Server 2016. Prior to that release, you would need to use SET CONTEXT_INFO, which provides a similar, but much more limited way of storing session state.

Continue reading →

Return Operating System Version Info in SQL Server with the sys.dm_os_host_info Dynamic Management View

Posted on by Ian

In SQL Server, you can use the sys.dm_os_host_info system dynamic management view to return information about the underlying operating system.

This view is similar to the sys.dm_os_windows_info view, except that sys.dm_os_host_info handles Linux systems as well as Windows, and it has more columns to differentiate between the two.

Continue reading →