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.
Category: DBMS
Database Management Systems
List All Nullable Columns in a SQL Server Database
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.
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).
Change the Password on the SA Login in SQL Server (T-SQL Example)
Below is an example of the T-SQL code you can use to change the password of the sa login account in SQL Server.
The sa account’s password can be changed with the ALTER LOGIN statement.
Rename the SA Account in SQL Server (T-SQL Example)
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.
Disable the SA Account in SQL Server (T-SQL Example)
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.
Non-Number Characters that Return Positive when using ISNUMERIC() in SQL Server
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.
How ISNUMERIC() Works in SQL Server
In SQL Server, you can use the ISNUMERIC() function to find out whether an expression is numeric or not.
The function returns 1 if the expression is numeric, and 0 if it’s not.
To use this function, simply pass the value/expression to the function while calling it.