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: employee ( employee_id , first_name ( ) , last_name ( ) ); CREATE TABLE INTEGER NOT NULL VARCHAR 42 NOT NULL VARCHAR 42 NOT NULL Good example: employee ( employee_id PRIMARY , first_name ( ) , last_name ( ) ); CREATE TABLE INTEGER NOT NULL KEY VARCHAR 42 NOT NULL VARCHAR 42 NOT NULL or employee ( employee_id , first_name ( ) , last_name ( ) , PK_Employee PRIMARY (employee_id) ); CREATE TABLE INTEGER NOT NULL VARCHAR 42 NOT NULL VARCHAR 42 NOT NULL CONSTRAINT KEY 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: books = = -- Noncompliant UPDATE SET title 'unknown' WHERE title NULL Good example: books = UPDATE 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: * my_table my_column ( nullable_column another_table) -- Noncompliant; may contain SELECT FROM WHERE NOT IN SELECT FROM "nullable_column" 'NULL' value and the whole SELECT query will return nothing Good example: * my_table ( another_table nullable_column = my_table.my_column) SELECT FROM WHERE NOT EXISTS SELECT 1 FROM WHERE or * my_table my_column ( nullable_column another_table nullable_column ) SELECT FROM WHERE NOT IN SELECT FROM WHERE 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 better performance, move the subquery out of the expression. and Note it is also an option to replace COALESCE with ISNULL. Bad example: WHEN ( COUNT A) + ... otherExpression CASE SELECT (*) FROM A) > 0 THEN (SELECT COUNT(*) FROM 42 ELSE END Good example: @a = (*) A @a > @a + ... otherExpression SET SELECT COUNT FROM CASE WHEN 0 THEN 42 ELSE END or ... (( a b c) , ) ... SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRANSACTION COALESCE SELECT FROM WHERE 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: @debug = NOCOUNT ; COUNT CREATE . @ ; PROCEDURE dbo MyProc (@debug INT) AS BEGIN DECLARE var INT IF 0 BEGIN SET ON END SELECT (*) FROM MY_TABLE END; or MyTrigger MyTable NOCOUNT ; [...] ; CREATE TRIGGER ON AFTER INSERT AS BEGIN SET 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: FIRST_NAME, LAST_NAME PERSONS LAST_NAME SELECT FROM WHERE LIKE '%PONT' 7. "CASE" input expressions should be invariant Under the covers, Simple CASE expressions are evaluated as searched CASE expressions. That is, @foo CASE WHEN 1 THEN 'a' WHEN 2 THEN 'b' is actually evaluated as = THEN = THEN CASE WHEN @foo 1 'a' WHEN @foo 2 '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: (SMALLINT, RAND()* ) THEN THEN CASE CONVERT @foo -- Noncompliant WHEN 1 'a' WHEN 2 'b' Good example: @bar = ( , ()*@foo) @bar DECLARE SMALLINT CONVERT SMALLINT RAND CASE 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: ... @id INSERT SET = 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
