Tips to increase your Transact-SQL efficiency Part 1.

Given below are little known tips that you can use to ensure your Transact-SQL queries are performing in the most efficient manner possible.

1. Avoid '*' in select query.

    

Restrict the queries result set by returning only the particular columns from the table and not all the table's columns. The sql query becomes faster if you use the actual column names in SELECT statement instead of than '*'.

2. Avoid COUNT(*) in select statement to check the existence of records in table.

     

Instead use IF EXISTS() to check records.

- Write the query as:

IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’)

- Instead of :

SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’

3. Use alternate of SELECT COUNT(*).

    

Use an alternative way instead of the SELECT COUNT(*) statement to count the number     of records in table.       

- SELECT COUNT(*) statement makes a full table scan to return the total table's row       count which can take an extremely long time for large tables.

Use alternate query to achieve the same

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

4. Use TOP keyword or the SET ROWCOUNT to fetch first Nth row from table.

     

- TOP or SET ROWCOUNT clause restricts the number of result and returns the smaller  result set. This helps to reduce the data trafic between server and client.

5. Use ORDER BY clause with Primary or Indexed column of table

- Fetching the result set order by primary column or Indexed column added big performance benefits as SQL server don't have to perform the extra overheads to rearrange data. Also try to implement the ORDER BY clause on Integer column instead of VARCHAR or STRING column.

6. Avoid ORDER BY on multiple columns.

- Implementing the order by clause on multiple column degrade the query performance as the SQL

server has to run data sorting algorithm independently on each column or result set.

7. Use 'WHERE' instead of 'HAVING'

- 'HAVING' clause is used to filter the rows after all the rows are selected. It is just like a filter who filter

data from selected list.

- 'WHERE' clause work along with select statement to select only respective rows Do not use HAVING

clause for any other purposes.

8. Avoid Mathematical expression on column.

Avoid mathematical expression on column in WHERE clause.

We should avoid computation on columns as far as possible and hence we will get an index scan

instead of a seek

- For example : SELECT * FROM Orders WHERE OrderID*3 = 33000

degrade the performace as query performing calculation on column

9. Minimize the number of subquery block in your query.

The more number of sub query makes the execution plan complicated. Keep the script logic as simple as possible.

10. Use 'LIKE' clause in query instead of SUBSTR() function.

The SUBSTR() function first extract the value from data and then matching result get added in result

set. LIKE clause has potential great advantage over SUBSTR() whenever string data need to compare

- Write the query as:

SELECT id, first_name, age FROM student_details WHERE first_name LIKE 'ABC%'

- Instead of :

SELECT id, first_name, age FROM student_details WHERE SUBSTR(first_name,1,3) = 'ABC';

My next post will add 10 more tips to increase your transact-sql efficiency... wait for it. 

Part 2 :- http://www.developerscloud.org/2013/10/tips-to-increase-your-transact-sql.html