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 →

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 →

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 →

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.

Continue reading →

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).

Continue reading →

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).

Continue reading →

The ISNUMERIC() function in SQL Server enables you to check whether or not an expression is numeric.

However, there may be times where you get results that you didn’t expect. This could happen if you have an expression that contains a character that is not a number, but is still accepted by ISNUMERIC() as being numeric.

There are a bunch of characters that ISNUMERIC() accepts as numeric that you might not have thought of as numeric. These include characters such as plus (+), minus (-), and the various currency symbols. Also, depending on its placement, the letter e could also allow the whole expression to be interpreted as numeric.

Continue reading →