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

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

Prepared by Bihag Thaker

Good Indentation makes queries more readable and easy to debug.

Try to break the queries into different lines by different query clauses like SELECT, FROM, WHERE, INNER JOIN, GROUP BY, HAVING, ORDER BY etc. Wherever possible, place column names on separate lines one level
indented to the right side.

If a column is not the first column in the list, place comma (,) separator before the name of the column. This helps queries easier to read and debug.

While debugging a query, anytime any line containing a column name can be easily commented out or
uncommented as per the need. Following is the example of the same:

1 2 3 4 5 6 7 8 9 10 11

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 ‘SELECT *’ in SELECT queries. Always specify the list of columns in SELECT queries.
Avoid following types of queries:

1	SELECT * FROM [dbo].[tbl_Customers]

Instead specify the list of columns even if all the columns are required in result set as shown below:

1 2 3 4 5 6 7

SELECT FirstName ,LastName ,BirthDate ,EmailAddress ,Gender FROM [dbo].[tbl_Customers]

Use of ‘SELECT *’ guarantees the same order of the column in result set every time.

This behaviour may cause problems when a column is inserted to or deleted from the table and can break the application when application is expecting only required fields.

If a column is removed from the table and query is using ‘SELECT *’, then it can go unnoticed and logical bugs can be introduced in code.

Also retrieve only columns which are required in the output to be given to the application.

Only retrieve the records which are required by the application. Try to limit the result set of queries by applying various filters with clauses like ON, WHERE and HAVING wherever possible.

This has a significant performance impact on very large tables. This practice also reduces network traffics by requiring the less number of bytes to transfer across the network.

For calculated columns, always specify aliases with ‘AS’ keyword rather than leaving a column unnamed as shown in following example:

1 2 3 4 5 6 7

SELECT FirstName + ' ' + LastName AS FullName ,CASE Gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Gender FROM [dbo].[tbl_Customers]

However column aliases can be assigned in a few different ways as shown below:

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT FirstName + ' ' + LastName AS FullName FROM [dbo].[tbl_Customers]     SELECT FullName = FirstName + ' ' + LastName FROM [dbo].[tbl_Customers]     SELECT FirstName + ' ' + LastName FullName FROM [dbo].[tbl_Customers]

Always use the technique of using AS keyword as a standard practice to specify column aliases.

Also observe the indentation of CASE expression used to distinguish the gender of a customer in previous query. Try to put every case on separate line one level indented to the right side enclosed with CASE and END body.

This practice makes very complex queries easier to understand and debug. An alias name must be assigned to such column expressions.