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.
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.
CREATE TABLE employee ( employee_id INTEGER NOT NULL, first_name VARCHAR(42) NOT NULL, last_name VARCHAR(42) NOT NULL );
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) );
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.
UPDATE books SET title = 'unknown' WHERE title = NULL -- Noncompliant
UPDATE books SET title = 'unknown' WHERE title IS NULL
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.
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
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)
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.
CASE WHEN (SELECT COUNT(*) FROM A) > 0 THEN (SELECT COUNT(*) FROM A) + 42 ... ELSE otherExpression END
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) ...
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.
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;
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.
SELECT FIRST_NAME, LAST_NAME FROM PERSONS WHERE LAST_NAME LIKE '%PONT'
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.
CASE CONVERT(SMALLINT, RAND()*@foo) -- Noncompliant WHEN 1 THEN 'a' WHEN 2 THEN 'b'
DECLARE @bar SMALLINT = CONVERT(SMALLINT, RAND()*@foo) CASE @bar WHEN 1 THEN 'a' WHEN 2 THEN 'b'
@@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.
INSERT ... SET @id = @@IDENTITY -- Noncompliant
INSERT ... SET @id = SCOPE_IDENTITY()
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:
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!