SQL Queries Scripting Tips - Part 1

SQL Queries that are mostly  used to select /retrieve data from the database. There are different ways to write the same SQL query result as per the requirment, but use of the best approach  is important when results are needed correct and faster. If we use the correct method for correct result is  definetly improve the performance of query excution and ultimatly it affects the overall applications performance. I have tried in this Article to help the beginners in writing SQL server queries effectively for database testing.

Tips for writing optimized SQL Queries

1.  Use of column names  instade of  '*'

The sql query will give the faster result if you use the exact columns names in SELECT statement instead of '*'. Hence remove * from SELECT and use only those columns which are necessary.

Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
For example:

SELECT empid, emp_name, age, deptno
                      FROM employee
                      

Select all Open in new window

Instead of

SELECT * from employee
                      

Select all Open in new window

2. Use joins and  Subqueries as per the requirment.

Joins and Subqueries both have there own advantages. Most of the time Joins re provide the better performance as subqueries have some restrictions.Try to select the best option for your query.

Join Advantages :
Joins are helpful when we need to retrive the data from multiple tables. If we try to use subqueries in this senario it will affect performance.

For Example :
If you want the data for Employee with department then use Correct Use of joins

Select emp.Empname , dept.DeptName 
                      from Employee emp 
                      inner join Department dept on emp.deptno=dept.deptno
                      

Select all Open in new window

Instead of
Wrong use of Sub queries (avoiding the Join)
In this case Sub queries are using full table scan which results in poor performance than above

Select emp.Empname , 
                       ( select deptname from Department dept where emp.deptno = dept.deptno) as deptname
                      from
                      Employee emp
                      

Select all Open in new window

3. Advantages  of Subqueries :
Subqueries provide the better performance when we need use expressions , which experssion values used in outer query for comparision. In this case we do not have pther option that sub query

For Example :
To Get the employee ID's of all employees with minimum salary.
This query is difficult to convert in joins.

SELECT EmployeeID,salary 
                      FROM Employee  
                      WHERE salary= (SELECT min(Salary)   FROM Employee)
                      

Select all Open in new window

Limitations of Subqueries
1. Subqueries must return Sing values which will use for comparision.
2. The Distinct clause can not be used in subqueries that include group by.
3. view created by subquery can not be updated.
4. ntext , text , image data types cannot use in select list of subqueries.

If you want to learn more about subuery fundamentals refer to:
http://technet.microsoft.com/en-us/library/ms189575.aspx.