How To Install Sql Server 2008 On Windows 10

Top 10 Microsoft SQL Server Tips

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 the language. A primary key should be specified during table creation to guarantee that all its records have primary key values. 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.

Nikita Starichenko Hacker Noon profile picture

@ wownetort
Nikita Starichenko

7+ years full-stack developer

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 resultsand 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