SQL Server Coding Standards: How to write a SELECT Query (Part 2)?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

Do not use ORDER BY clause unnecessarily in queries until the result set is strictly required to be sorted in a particular order. ORDER BY clause is an expensive operation and can cause performance issues.

Always specify the name of the columns in ORDER BY clause rather than specifying the position of the column in the SELECT list. For Example, write the query as:

1 2 3 4 5

SELECT OrderID ,OrderDate FROM [dbo].[tbl_Orders] ORDER BY OrderDate DESC

Do not write above query as follows:

1 2 3 4 5

SELECT OrderID ,OrderDate FROM [dbo].[tbl_Orders] ORDER BY 2 DESC

Whenever it is safe and OK to for reading dirty data, include WITH (NOLOCK) query hint in SELECT queries. It reduces locking issues and retrieves data faster. See the following example:

1 2 3 4 5 6

SELECT OrderID ,CustomerID ,OrderDate FROM [dbo].[tbl_Orders] WITH (NOLOCK) WHERE OrderDate>='20180101' AND OrderDate<='20180401'

Do not use old syntax for joining the tables in queries. Use ANSI-SQL standard syntax and use keywords for joining the tables like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN etc.

Beware while joining tables in queries. Prefer INNER JOIN over OUTER JOIN wherever it is possible to use them and do not use OUTER JOIN unnecessarily as they are less efficient then INNER JOIN.

Beware of using OUTER JOIN correctly. If you have LEFT OUTER JOIN and have specified a WHERE condition on a column from the right table, then the query is equal to INNER JOIN and is logically incorrect. Such queries must be most likely to use INNER JOIN instead of OUTER JOIN.

While joining the tables, make use of proper aliases for the tables and prefix each column name with its associated table alias. Do not use long names for table aliases.

Keep them short and abbreviated by specifying the first character of a word in the table name. For example, use OD as an alias for table tblOrderDetails and PC for table tblProductCategories. Following is an example of table alias in join queries.

1 2 3 4 5 6 7 8 9 10

SELECT C.CustomerID ,C.FirstName ,C.LastName ,COUNT(O.OrderID) AS TotalOrders FROM [dbo].[tbl_Customer] AS C INNER JOIN [dbo].[tbl_Orders] AS O ON C.CustomerID = O.CustomerID WHERE OrderDate>='20180101' GROUP BY C.CustomerID HAVING COUNT(OrderID) > 3

Do not use DISTINCT keyword when GROUP BY clause is present in the same query because GROUP BY clause itself results in an only unique combination of columns. Thus DISTINCT is not of meaning when GROUP BY clause is present.

While specifying conditions in WHERE clause, try not to use <> and NOT operators wherever possible. They tend to introduce query performance issues.

Avoid using LIKE operator as much as possible. Using like operator is not efficient and degrades the query performance. LIKE operator should be avoided in WHERE clause when the first character(s) is specified with a wildcard character.
This prevents the use of the index and retrieves rows inefficiently. So avoid queries similar to below:

1 2 3 4 5 6 7 8

SELECT FirstName ,LastName ,BirthDate ,EmailAddress FROM [dbo].[tbl_Customers] WHERE LastName LIKE '%Patel%' ORDER BY LastName,FirstName

While specifying date literals in WHERE clause, do not use date formats which rely on date format set for a particular session. Always use the universal date format which is ‘YYYYMMDD’.

This format guarantees to work correctly irrespective of date format set for a session. For example to retrieve all orders for year 2010, use the following date values in WHERE clause:

1 2 3 4 5 6

SELECT OrderID ,OrderDate FROM [dbo].[tbl_Orders] WHERE OrderDate >= '20180101' AND OrderDate <= '20180430' ORDER BY OrderDate DESC

Whenever possible, try to avoid the use of scalar functions in WHERE clause. For example, the previous query retrieves all the orders for the year 2010. Same results can be achieved by using YEAR() function as shown below:

1 2 3 4 5 6

SELECT OrderID ,OrderDate FROM [dbo].[tbl_Orders] WHERE YEAR(OrderDate) = 2010 ORDER BY OrderDate DESC

Such careless use of functions in WHERE clause should be avoided when there is an alternative solution. Such usages of functions in queries can degrade the performance of the queries.

Likewise, avoid unnecessary use of CAST() and CONVERT() functions to convert between the data types. Also, minimize the use of other scalar functions as they degrade the performance of the queries.

In case, the same value of a scalar function needs to be used at multiple places in a query or a single batch, then consider storing the value of a scalar function in a variable and subsequently use that variable at other places.

Never use comparison operators like ‘equals to’ (=), ‘not equal to’ (<>) to compare NULL values in WHERE clause. Always use IS NULL and IS NOT NULL operators to compare NULL values. So, instead of writing the query as:

1 2 3 4 5 6 7

SELECT FirstName ,LastName ,BirthDate ,EmailAddress FROM [dbo].[tbl_Customers] WHERE EmailAddress=NULL

Write the above query correctly as follows:

1 2 3 4 5 6 7

SELECT FirstName ,LastName ,BirthDate ,EmailAddress FROM [dbo].[tbl_Customers] WHERE EmailAddress IS NOT NULL

The result of comparing NULL values with comparison operators depends on the SQL Server setting
SET ANSI_NULLS. So, it should never be used.