Tag: t-sql

List All Nullable Columns in a SQL Server Database

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

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 →

Use NEWSEQUENTIALID() to Create an Incrementing GUID in SQL Server

Posted on by Ian

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 →

Use NEWID() to Create a Unique Value in SQL Server

Posted on by Ian

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 →

Rename the SA Account in SQL Server (T-SQL Example)

Posted on by Ian

A common security precaution you should consider when administering SQL Server is to rename the sa login.

In addition to renaming this account, you should also disable it altogether and then create other admin accounts to be used instead of sa.

The sa account is widely known as “THE” admin account and therefore it’s a perfect target for hackers. Disabling this login prevents hackers from being able to use it to hack your system, and renaming the account can provide an extra layer of protection.

Continue reading →

Disable the SA Account in SQL Server (T-SQL Example)

Posted on by Ian

The sa login is the most famous login account in SQL Server. Every SQL Server administrator knows about it (if they don’t, they should).

Due to its administrative nature, any compromise of the sa account could have catastrophic consequences, including the complete loss of control over SQL Server.

It is therefore widely considered good security practice to disable the sa account altogether. And as an extra precaution, rename the account and change its password. Other admin accounts can be created as required.

Continue reading →

Non-Number Characters that Return Positive when using ISNUMERIC() in SQL Server

Posted on by Ian

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 →

How to Delete Files in SQL Server 2019

Posted on by Ian

In SQL Server 2019, you can use the sys.xp_delete_files stored procedure to delete a file on the file system.

This stored procedure was introduced in SQL Server 2019, and it can be used in conjunction with sys.xp_copy_file and sys.xp_copy_files (both of which were also introduced in SQL Server 2019), which enable you to copy files.

Prior to SQL Server 2019, you would need to use xp_cmdshell, which spawns a Windows command shell and passes in a string for execution. The new stored procedures introduced in SQL Server 2019 allow you to copy and delete files without relying on xp_cmdshell.

Continue reading →