<

SQL Queries Scripting Tips - Part 1

Published on
9,504 Points
3,404 Views
1 Endorsement
Last Modified:
Approved
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

Open in new window


Instead of

SELECT * from employee

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

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

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)

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.
1
Comment
0 Comments

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month