When you use T-SQL to create (or alter) a table in SQL Server, you have the option of using the IDENTITY() property to create an identity column.
An identity column contains an automatically incrementing identification number. The IDENTITY() property can be used with the CREATE TABLE and ALTER TABLE statements.
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.
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.
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.
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.
Most people who use SQL Server, know about the convention of adding GO to the end of each batch of T-SQL statements. But perhaps less commonly known, is that you can also add an integer after GO to specify how many times the batch should run.
Nullable columns in a database can sometimes lead to performance issues. That is definitely not to say that nullable columns will always cause performance issues, but if you do happen to have performance issues, identifying nullable columns can potentially provide some clues as to where the issue lies. Sometimes making a column NOT NULL can help improve performance.
By “nullable columns” I mean, columns that allow for NULL. If the column’s definition doesn’t include NOT NULL, then it allows for NULL values and it’s “nullable”.
Below is code that allows you to list all nullable columns in a database in SQL Server.
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.
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).
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).