Top 10 Microsoft SQL Server Tips

Hi everyone! There is a lot of information about different T-SQL features. About various life hacks and best practices in this language. I want to tell you about equally useful, but less popular tips for working with this language.

1. A primary key should be specified during table creation

Tables without primary keys are largely unusable in a relational database because they cannot be joined to. A primary key should be specified at table creation to guarantee that all its records have primary key values.

Bad example:

CREATE TABLE employee
(
  employee_id INTEGER NOT NULL,
  first_name VARCHAR(42) NOT NULL,
  last_name VARCHAR(42) NOT NULL
);

Good example:

CREATE TABLE employee
(
  employee_id INTEGER NOT NULL PRIMARY KEY,
  first_name VARCHAR(42) NOT NULL,
  last_name VARCHAR(42) NOT NULL
);

CREATE TABLE employee
(
  employee_id INTEGER NOT NULL,
  first_name VARCHAR(42) NOT NULL,
  last_name VARCHAR(42) NOT NULL,
  CONSTRAINT PK_Employee PRIMARY KEY (employee_id)
);

2. "NULL" should not be compared directly

In a Zen-like manner, "NULL" is never equal to anything, even itself. Therefore comparisons using equality operators will always return False, even when the value actually IS NULL.

For that reason, comparison operators should never be used to make comparisons with NULL; IS NULL and IS NOT NULL should be used instead.

Bad example:

UPDATE books
SET title = 'unknown'
WHERE title = NULL -- Noncompliant

Good example:

UPDATE books
SET title = 'unknown'
WHERE title IS NULL

3. Nullable subqueries should not be used in "NOT IN" conditions

A WHERE clause condition that uses NOT IN with a subquery will have unexpected results if that subquery returns NULL. On the other hand, NOT EXISTS subqueries work reliably under the same conditions.

This rule raises an issue when NOT IN is used with a subquery. This rule doesn't check if the selected column is a nullable column because the rules engine has no information about the table definition. It's up to the developer to review manually if the column is nullable.

Bad example:

SELECT *
FROM my_table
WHERE my_column NOT IN (SELECT nullable_column FROM another_table)  -- Noncompliant; "nullable_column" may contain 'NULL' value and the whole SELECT query will return nothing

Good example:

SELECT *
FROM my_table
WHERE NOT EXISTS (SELECT 1 FROM another_table WHERE nullable_column = my_table.my_column)

SELECT *
FROM my_table
WHERE my_column NOT IN (SELECT nullable_column FROM another_table WHERE nullable_column IS NOT NULL)

4. "COALESCE", "IIF", and "CASE" input expressions should not contain subqueries

COALESCE and IIF (which evaluate to CASE expressions under the covers), as well as CASE input expressions should not be used with subqueries because the subquery will be evaluated once for each option in the expression, and each evaluation could return different results depending on the isolation level. To ensure consistent results, use the SNAPSHOT ISOLATION isolation level. To ensure consistent results and better performance, move the subquery out of the expression.

Note it is also an option to replace COALESCE with ISNULL.

Bad example:

CASE
WHEN (SELECT COUNT(*) FROM A) > 0 THEN (SELECT COUNT(*) FROM A) + 42
...
ELSE otherExpression
END

Good example:

SET @a = SELECT COUNT(*) FROM A

CASE
WHEN @a > 0 THEN @a + 42
...
ELSE otherExpression
END

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
...
COALESCE((SELECT a FROM b WHERE c) , 1)
...

5. "NOCOUNT" should be activated on "PROCEDURE" and "TRIGGER" definitions

NOCOUNT is by default deactivated (OFF) at server level. It means by default, the server will send to the client the number of rows affected by the SQL query executed which is, in most cases, useless because no one will read this information.

Deactivating this feature will save some network traffic and improve the execution performance of stored procedures and triggers that's why it is recommended to define SET NOCOUNT ON at the beginning of the definition of PROCEDUREs and TRIGGERs, before any query is processed.

This rule raises an issue when NOCOUNT is not set or is set to OFF between the beginning of the PROCEDURE (or TRIGGER) definition and the first statement that is not a SET, IF or DECLARE.

Good example:

CREATE PROCEDURE dbo.MyProc(@debug  INT)
AS
BEGIN
  DECLARE @var INT;
  IF @debug = 0
    BEGIN
      SET NOCOUNT ON;
    END
  SELECT COUNT(*) FROM MY_TABLE
END;

CREATE TRIGGER MyTrigger ON MyTable
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;
  [...]
END;

6. "LIKE" clauses should not start with wildcard characters

When the value of a LIKE clause starts with '%', '[...]' or '_', indexes on the searched column are ignored, and a full table scan is performed instead.

Bad example:

SELECT FIRST_NAME, LAST_NAME FROM PERSONS
WHERE LAST_NAME LIKE '%PONT'

7. "CASE" input expressions should be invariant

Under the covers, Simple CASE expressions are evaluated as searched CASE expressions. That is,

CASE @foo
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'

is actually evaluated as

CASE
WHEN @foo = 1 THEN 'a'
WHEN @foo = 2 THEN 'b'

In most situations the difference is inconsequential, but when the input expression isn't fixed, for instance if RAND() is involved, it is likely to yield unexpected results. For that reason, it is better to evaluate the input expression once, assign it to a variable, and use the variable as the CASE's input expression.

This rule raises an issue when any of the following is used in a CASE input expression: RAND, NEWID, CRYPT_GEN_RANDOM.

Bad example:

CASE CONVERT(SMALLINT, RAND()*@foo)  -- Noncompliant
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'

Good example:

DECLARE @bar SMALLINT = CONVERT(SMALLINT, RAND()*@foo)
CASE @bar
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'

8. "@@IDENTITY" should not be used

@@IDENTITY returns the last identity column value created on a connection, regardless of the scope. That means it could return the last identity value you produced, or it could return a value generated by a user defined function or trigger, possibly one fired because of your insert. In order to access the last identity value created in your scope, use SCOPE_IDENTITY() instead.

Bad example:

INSERT ...

SET @id = @@IDENTITY  -- Noncompliant

Good example:

INSERT ...

SET @id = SCOPE_IDENTITY()

9. Deprecated features should not be used

Deprecated language features are those that have been retained temporarily for backward compatibility, but which will eventually be removed from the language. In effect, deprecation announces a grace period to allow the smooth transition from the old features to the new ones. In that period, no use of the deprecated features should be added to the code, and all existing uses should be gradually removed.

The following features are deprecated or (depending on your version) already removed from TSQL:

Instead of WRITETEXT, UPDATETEXT, READTEXT - Use VARCHAR
Instead of GROUP BY ALL - Use UNION or a derived table
Instead of FASTFIRSTROW - Use FAST n
Instead of SETUSER - Use EXECUTE AS
Instead of CREATE RULE - Use CHECK constraints
Instead of PASSWORD, MEDIAPASSWORD in BACKUP or RESTORE - Use use access control on the directory or WITH RESTRICTED_USER for RESTORE
Instead of DBCC DBREINDEX - Use ALTER INDEX REBUILD
Instead of DBCC INDEXDEFRAG - Use ALTER INDEX DEFRAG
Instead of DBCC SHOWCONTIG - Use sys.dm_db_index_physical_stats
Instead of DBCC CONCURRENCYVIOLATION - Use no replacement
Instead of SET ROWCOUNT - Use TOP(n)
Instead of SET REMOTE_PROC_TRANSACTIONS - Use distributed queries that reference linked servers (sp_addlinkedserver)
Instead of RAISERROR @Errno @ErrMsg - Use the RAISERROR syntax using parentheses
Instead of DROP INDEX with two-part name - Use Move table (and database) to an ON clause
Instead of String literals as column aliases - Use Remove the quotes around the alias. Use square brackets if escaping is necessary
Instead of Numbered procedures - Use Replace with un-numbered procedures
Instead of System stored procedure - Use no replacement
Instead of fn_virtualservernodes - Use sys.dm_os_cluster_nodes
Instead of fn_servershareddrives - Use sys.dm_io_cluster_shared_drives
Instead of GRANT / DENY / REVOKE ALL - Use List the specific permissions
Instead of TORN_PAGE_DETECTION - Use checksum
Instead of TAPE - Use DISK or URL
Instead of Table hint without - Use WITH WITH(hint)

10. "GOTO" statements should not be used

A GOTO statement is an unstructured change in the control flow. They should be avoided and replaced by structured constructs.

P.S. Thanks for reading! More tips coming soon!

Special thanks to SonarQube and their rules - https://www.sonarqube.org/

More tips: Top 25 C# Programming Tips