Good habits when writing T-SQL statements (Part 1)

Application performance depends on a lot of factors, including a very important factor that is the time for SQL server to process T-SQL statements. Sometimes database design and complex query requirements impede the execution speed of T-SQL statements. Writing code for each T-SQL statement can also make the SQL server have to work harder to process the query. The following article will introduce you to good habits to practice when writing T-SQL code. Thereby you can understand how to write optimal queries, make good use of SQL server resources and improve performance.

Specify the column name in the SELECT statement

 SELECT * FROM MyTable; 

How many times have you written the above statement?

Using an asterisk (*) tells the database that you want to return all columns from the table (or tables) declared in the FROM clause. This is not a good habit even if you want all columns to be returned to the application. It is better to specify the name of each column in the table as follows:

 SELECT ID, Description, DateModified FROM MyTable; 

Clearly declaring the name of the columns in the SELECT statement brings a lot of benefits. First, the SQL server will only return the data needed for the application, not a pile of data in which many of your applications do not need it. By only asking for the data you need to return, you have contributed to optimizing the amount of SQL server work to perform to collect all the columns of the information you require. Also, by not using an asterisk (*), you have minimized the network traffic (the number of bytes) needed to send data related to the SELECT statement to the application.

In case you use an asterisk (*) and someone has added a new column to the table, your application will start receiving data for this column without changing the application source code. If your application waits for a certain number of columns to be returned, it will fail as soon as someone adds a new column to one of the reference tables. By explicitly declaring each column name in the SELECT statement, the application will always receive a fixed number of return columns, even if someone adds a new column to the reference tables in the SELECT statement. As a result, you have helped the application avoid potential risks associated with database changes that may occur with any table you refer to in the SELECT statement.

Specify the column name in the INSERT statement

As above, you should specify each column name you want to insert data into the INSERT statement. Do not write the INSERT statement as follows:

 INSERT INTO MyTable VALUES ('A','B','C'); 

When you write this type, the SQL server requires only three correct columns defined in the MyTable table, and the 'A' value will be inserted into the first column, 'B' in the second column, 'C' in the last column. . If someone adds a new column to the MyTable table, your application will get an error:

 Msg 213, Level 16, State 1, Line 1 
 Column name or number of supplied values does not match table definition. 

(Column name or number of values ​​does not match the table)

Therefore, instead of writing the above INSERT statement, you should write the following:

 INSERT INTO MyTable(So1, So2, So3) VALUES ('A','B','C'); 

By writing on, when someone adds a column called 'So4' to the MyTable table, the INSERT statement continues to work as long as the 'So4' column is created with the default value of DEFAULT or NULL enabled.

Add prefix for wildcard to speed up search

Using appropriate wildcards can improve query performance. For example, you want to search in the AdventureWorks.Person.Contact table, all LastNames end with 'sen'. Suppose that you have built an index on the LastName column. If you write the search statement as follows:

 SELECT Distinct LastName 
 FROM Person.Contact 
 WHERE LastName LIKE '%sen' 

The statement uses a percent character (%) to replace zero or more characters followed by the string 'sen' in the LastName field. This causes the SQL server to perform an index scan to search for all names that end with 'sen' to resolve the query. This is significant because until the entire table is scanned, the SQL server cannot guarantee that all records that have LastName ended with 'sen' are found.

Also, if you are looking for records with LastName that are six characters long and ending with 'sen', you can write the search statement as follows:

 SELECT Distinct LastName 
 FROM Person.Contact 
 WHERE LastName LIKE '___sen' 

Here, the statement uses the underscore character (_) to replace a single character. This example is similar to the example above and uses index scanning to resolve. Again, the SQL server knows that it is necessary to scan the entire index before making sure all six-letter names are found and ending with 'sen' in the Person.Contact table.

The SQL server may return results faster if it does not have to read the entire index using the scan engine. SQL Server is smart enough to know when you prefix an alternative character (%, _, etc.), it can use an index search operation to resolve search criteria. The following is an example of a request search statement that returns all records with LastName starting with the character 'A' and ending with 'sen':

 SELECT Distinct LastName 
 FROM Person.Contact 
 WHERE LastName LIKE 'A%sen' 

By placing the character 'A' in front of the percent sign (%) in the search statement, you tell the SQL server that it can use an index search operation to resolve the query. Once the SQL server reads the last record with LastName beginning with the letter 'A', it knows that no record with LastName begins with the letter 'A' and will stop.

Not all replacement characters need an additional prefix for the SQL server to use an index search operation to resolve the query. The following is an example in which the statement uses an expression that represents an alternate character set and still allows the SQL server to resolve the query using an index search operation:

 SELECT Distinct LastName 
 FROM Person.Contact 
 WHERE LastName LIKE '[AM]%sen' 

The Last T-SQL statement on the LastName search begins with any character between 'A' and 'M' and ends with 'sen'. Syntaxes that use alternative characters in which the set of characters can be specified can also call index scan operations to resolve search criteria.

Use DISTINCT only when needed

Placing the DISTINCT keyword in the SELECT statement will remove duplicate results from the returned results of the query. It causes the SQL server to perform an SORT operation to sort the data to identify and remove duplicates. Therefore, if you anticipate that the returned results will not duplicate, you should not use the keyword DISTINCT in the T-SQL statement. With the use of the DISTINCT keyword in the query, you asked the SQL server to perform the sort operation to remove duplicate results. This is an extra part of the SQL server and it doesn't make sense if your result set only includes unique records.

Use UNION only when needed

Just like in the case of the DISTINCT keyword, the UNION operator requires adding a SORT operation so that the SQL server can remove duplicate results. If you anticipate the list of results returned with no similar results, the sorting operation that the SQL server must perform becomes unnecessary. So when you need to use the UNION operator to join two sets of records together, where the records are unique and don't overlap, you'd better use the UNION ALL operator. The UNION ALL operator does not remove duplicate records, thus mitigating the task for the SQL server during processing because the sort operation is not required. Reducing the work for SQL server means that processing will be done faster.

Apply good habits to make code faster

There are many reasons to train yourself to get used to optimal code writing. When you master the small tips above and make it a habit every time you write a T-SQL statement, you can avoid the risk that may occur when the database changes, while improving performance. server performance by minimizing network traffic. These simple tips also help you make better use of server resources during command processing.