T-SQL Query Performance Tunning Tricks

I am trying to add some T-SQL query performance tricks for SQL server developers. Some of are very obvious but still i have seen professional developers not uses these.

1. IN / BETWEEN

When you have choice of using IN / BETWEEN in TSQL, BETWEEN is good choice.

SELECT  eid,ename,salary FROM Employee WHERE eid IN (1,2,3,4,5,6,7,8,9,10)
SELECT eid,ename,salary FORM Employee WHERE eid BETWEEN 1 AND 10

BETWEEN is much more efficient than IN.

2. SUBSTRING/LIKE

Sometime it is possible to replace SUBSTRING character function with LIKE. SUBSTRING function forces the Table scan istead of allowing optimizer to use and Index.

SELECT eid,ename,salary FROM Employee WHERE SUBSTRING(ename,1,1) = ‘S’
SELECT eid,ename,salary FROM Employee WHERE ename LIKE ‘S%’

Both query returns the same result but LIKE clause gives the batter performance in this case.

3. STRING FUNCTION , if possible avoid it.

SELECT eid,ename FROM Employee WHERE UPPER(ename) = ‘XYZ’

We can rewrite the same query
SELECT eid,enam FROM Employee WHERE ename = ‘XYZ’ or ename = ‘xyz’

Second query run much more faster than the first query which has UPPER function.


4. Union / Union All 

If using the UNION statement, keep  in mind that, by default, UNION performs the  similar of a SELECT DISTINCT on the final output of the result query. In other words, UNION takes the results of two like  recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows.

This process occurs even if there are no duplicate records in the final recordset.
If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows. But if not, use UNION ALL, which is less resource intensive.