<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL Queries Scripting Tips - Part 1

Published on
9,579 Points
3,479 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

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Join & Write a Comment

Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month