sql server query optimization tips

• 
  QUERY OPTIMIZATION SQL
•   The sql query becomes faster if you use the actual columns names in                               SELECT statement instead of than '*'
•  Select carefully where we have to use “where” and “having”

·         Sometimes you may have more than one sub queries in your main query. Try to minimize the number of sub query block in your query. 
For Example: Write the query as

“SELECT name
FROM employee
WHERE (salary, emp_age) = (SELECT MAX (salary), MAX (emp_age)
FROM employee_details)
AND dept = 'Electronics'; “

Instead of:

“SELECT name
FROM employee
WHERE salary = (SELECT MAX (salary) FROM employee_details)
AND age = (SELECT MAX (emp_age) FROM employee_details)
AND emp_dept = 'Electronics';”

·         Usually “IN” has the slowest performance, “IN” is efficient when most of the filter criteria is in the sub-query

·         “EXISTS” is efficient when most of the filter criteria is in the main query

·         Use “EXISTS” instead of “DISTINCT” when using joins which involves tables having one-to-many relationship. 
For Example: Write the query as

SELECT d.dept_id, d.dept_name
FROM dept d
WHERE EXISTS (SELECT 'X' FROM employee e WHERE e.dept_name = d.dept_name);

Instead of:

SELECT DISTINCT d.dept_id, d.dept_name
FROM dept d,employee e
WHERE e.dept_name = e.dept_name;

·         Try to use UNION ALL in place of UNION

·         Be careful while using conditions in WHERE clause. 
For Example: Write the query as

SELECT id, name, age FROM student WHERE age > 30;

Instead of:

SELECT id, name, age FROM student WHERE age! = 50;