SQL SERVER – Subquery or Join – Various Options – SQL Server Engine Knows the Best – Part 2

This blog post is part 2 of the earlier written article SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best by Paulo R. Pereira.

Paulo has left excellent comment to earlier article once again proving the point that SQL Server Engine is smart enough to figure out the best plan itself and uses the same for the query. Let us go over his comment as he has posted.

“I think IN or EXISTS is the best choice, because there is a little difference between ‘Merge Join’ of query with JOIN (Inner Join) and the others options (Left Semi Join), and JOIN can give more results than IN or EXISTS if the relationship is 1:0..N and not 1:0..1.

And if I try use NOT IN and NOT EXISTS the query plan is different from LEFT JOIN too (Left Anti Semi Join vs. Left Outer Join + Filter)”

USE AdventureWorks
GO
-- use of SOME
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = SOME (
SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
UNION ALL
SELECT EA.EmployeeID
FROM HumanResources.EmployeeDepartmentHistory EA
)
-- use of IN
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID IN (
SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
UNION ALL
SELECT EA.EmployeeID
FROM HumanResources.EmployeeDepartmentHistory EA
)
-- use of EXISTS
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS (
SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
UNION ALL
SELECT EA.EmployeeID
FROM HumanResources.EmployeeDepartmentHistory EA
)

When looked into execution plan of the queries listed above indeed we do get different plans for queries and SQL Server Engines creates the best (least cost) plan for each query. Click on image to see larger images.

inexists2 SQL SERVER   Subquery or Join   Various Options   SQL Server Engine Knows the Best   Part 2

Thanks Paulo for your wonderful contribution.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best

This is followup post of my earlier article SQL SERVER – Convert IN to EXISTS – Performance Talk, after reading all the comments I have received I felt that I could write more on the same subject to clear few things out.

First let us run following four queries, all of them are giving exactly same resultset.

USE AdventureWorks
GO
-- use of =
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
-- use of in
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID IN ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
-- use of exists
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
-- Use of Join
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress EA ON E.EmployeeID = EA.EmployeeID
GO

Let us compare the execution plan of the queries listed above. Click on image to see larger image.

inexists1 SQL SERVER   Subquery or Join   Various Options   SQL Server Engine knows the Best

It is quite clear from the execution plan that in case of IN, EXISTS and JOIN SQL Server Engines is smart enough to figure out what is the best optimal plan of Merge Join for the same query and execute the same. However, in the case of use of Equal (=) Operator, SQL Server is forced to use Nested Loop and test each result of the inner query and compare to outer query, leading to cut the performance. Please note that here I no mean suggesting that Nested Loop is bad or Merge Join is better. This can very well vary on your machine and amount of resources available on your computer.

When I see Equal (=) operator used in query like above, I usually recommend to see if user can use IN or EXISTS or JOIN. As I said, this can very much vary on different system. What is your take in above query? I believe SQL Server Engines is usually pretty smart to figure out what is ideal execution plan and use it.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 Star Join Query Optimization

Business Intelligence (BI) plays a significant role in businesses nowadays. Moreover, the databases that deal with the queries related to BI are presently facing an increase in workload. At present, when queries are sent to very large databases, millions of rows are returned. Also the users have to go through extended query response times when joining multiple tables are involved with such queries. ‘Star Join Query Optimization’ is a new feature of SQL Server 2008 Enterprise Edition. This mechanism uses bitmap filtering for improving the performance of some types of queries by the effective retrieval of rows from fact tables.

Improved Query Response Times
In general, data warehouses employ dimensionally modeled star or snowflake schemas. These schemas have one or more than one fact tables that contain transactional data and many dimension tables, which holds information such as product data, customer information, and times and dates – all these define the fact table data. Usually, foreign keys are employed for maintaining relationships between the rows in fact tables and also between the rows in the dimension tables. Databases that contain star schemas are recognized by SQL Server 2008 Enterprise. It uses the new Star Join Query logic for processing queries against such star schemas more efficiently. Typically, on an average, data warehouse queries run faster to approximately 20 percent.

Automatically Implemented
Star Join Query Optimization is automatically implemented by the SQL Server. It does not require a special database or application configuration. The query processor will usually optimize queries with medium selectivity (this refers to the queries that retrieve approximately 10% to 75% of rows from a fact table). Such queries are usually handled using hash joins to join the dimension and fact tables by employing the foreign keys to identify the matching rows. A hash table is built for each dimension table referenced in the query in the case of hash joins; the optimization process uses these hash tables for deriving bitmap filters. The key values from each dimension table are identified by bitmap filters; these key values qualify for inclusion in the query. When the fact table is scanned, the bitmap filters are applied to it. These bitmap filters eliminate those rows of the fact table which are not qualified for inclusion in the result set. The most selective bitmap filter is applied first as it is found to eliminate the highest number of rows. Since the eliminated rows do not need further processing, the subsequent filters need not be applied to them – this way the process becomes more efficient.

Query Selectivity
The performance is enhanced in the case of medium selectivity queries while using bitmap filtering because the rows are filtered before any joins are implemented. Hence, there is a decrease in the number of rows that are processed by each join. Bitmap filtering is not applied when queries are highly selective (i.e., those queries that return less than 10% of the rows in a fact table). In such case, a nested loop join is found to be generally more efficient. Similarly, when the queries are not very selective at all (queries which return more than 75% of the rows in a fact table), bitmap filtering is not applied as there are very few rows to be filtered, and hence, there is no requirement of enhancement in performance in this case.

Integer Data Types
Star join optimization is found to give the highest efficiency when the data type of the columns used in the joins is integer. This feature enables the bitmap filter to be applied as part of the initial table or index scan rather than being used at a later stage in the query plan. Most of the queries are benefited from star join optimization since foreign key relationships are commonly implemented using integer-based alternate key columns.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Understanding Table Hints with Examples

Introduction

Today we have a very interesting subject to look at. I tried to look for help online but have not found any other documentation besides what we have from the Book Online.

Let us try to understand what are the different kinds of hints available in SQL Server and how they are helpful.

What is a Hint?

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.

Before we continue to explore this subject, we need to consider one very important fact and say some words of caution. SQL Server Query optimizer is a very smart tool and it makes a best selection of execution plan. Suggesting hints to the Query Optimizer should be attempted when absolutely necessary and by experienced developers who know exactly what they are doing (or in development as a way to experiment and learn).

There are three different kinds of hints. Let us understand the basics of each of them separately.

Join Hint

This hint is used when more than one table is used in a query. Two or more tables can be joined using different kinds of joins. This hint forces the type of join algorithm that is used. Joins can be used in SELECT, UPDATE and DELETE statements.

Query Hint

This hint is used when certain kind of logic has to be applied to a whole query. Any hint used in the query is applied to the complete query, as opposed to part of it. There is no way to specify that only a certain part of a query should be used with the hint. After any query, the OPTION clause is specified to apply the logic to this query. A query always has any of the following statements: SELECT, UPDATE, DELETE, INSERT or MERGE (SQL 2K8); and this hint can be applied to all of them.

Table Hint

This hint is used when certain kind of locking mechanism of tables has to be controlled. SQL Server query optimizer always puts the appropriate kind of lock on tables, when any of the Transact SQL operations SELECT, UPDATE, DELETE, INSERT or MERGE are used. There are certain cases when the developer knows when and where to override the default behavior of the locking algorithm and these hints are useful in those scenarios.

Let us run the following simple query with different kinds of query hints and observe the actual execution plan. The analysis of execution plan is not part of this article and will be covered in future.

USE AdventureWorks
GO
/* No Query Hint */
SELECT *
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Merge Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER MERGE JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Hash Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER HASH JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Loop Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER LOOP JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Remote Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER REMOTE JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO

The above query will produce the following query execution plan.

sql join SQL SERVER   Understanding Table Hints with Examples

In this article we will focus mainly on Join Hints. We will discuss other kinds of hints and their usage in a different article. There are a total of four kinds of join hints available.

Loop Join

This join is also commonly known as nested iteration. This kind of join is composed by an outer loop and an inner loop. When the query runs for each row of the outer loop, the inner loop is executed completely. This join is effective only when the outer loop query is small and the inner loop query has all the proper optimizations applied. This join method is very useful with small transactions.

Merge Join

This join has the unique requirement for tables involved in the operation to be sorted. This join keeps both of the tables sorted in parallel and compares each table row by row simultaneously with each other. It compares one row of the first table with one row of the second table. If they are equal, that row qualifies; otherwise, this join determines which row of each table has the lower value. Once the lowest value of the table is figured out, it moves on to next row of that table and compares that to the original row. This operation keeps going on until all rows from each table are completely examined. This operation can be very expensive when tables are not sorted and it’s required to sort them before they are joined. If tables have non clustered indexes over them and joins are using the same conditions, there are pretty good chances that this join performs better than other kinds of joins.

Hash Join

This is the most complex of all the other joins. There are two major components of this kind of join – build query and probe query. First, a smaller table is assigned as build query and a hash table for the same is created. This hash table is compared with the probe table. This comparison of input table and probe table is done one row at a time. One row of the probe table is hashed and compared against the other row, and qualifying rows are checked.

There are three different kinds of hash joins:  in-memory hash join, grace hash join, and recursive hash join. Let us look in more detail at these three kinds of joins.

In-Memory Hash Join

This kind of hash join is used when tables are very small and a complete table can be hashed and loaded in memory.

Grace Hash Join

This kind of hash join is used when tables are comparatively large and don’t fit in memory. Tables will be partitioned at input levels and processed in steps.

Recursive Hash Join

This kind of join is used for complex tables and for tables which are very large and require multilevel of table partitions.

SQL Server query optimizer is a smart tool and it knows when to use the right kind of join. When it comes to hash join, the query optimizer starts conservatively with in-memory hash join. If join is larger than in-memory it moves up to Grace hash join or Recursive hash join. Sometimes the optimizer makes a mistake in identifying a smaller table and it reverses the role of the build and probe table: this is called role reversal.

Remote Join

This is least-used tabled join ever. There is no example for it given in Book On Line. Every Join has two tables associated with it: Left Table and Right Table. The join usually happens on the Left Table. When Remote close is used, joins are performed on the site of Right Join. This join can only be performed on INNER JOIN as in case of OUTER join there may be NULL values on the right table which makes concept of joining on right table logically incorrect.

Summary

We have closely observed different kinds of Join Hints in this article. We will cover the remaining concepts in another article.

Reference: Pinal Dave (http://blog.SQLAuthority.com), DNS

SQL SERVER – Interesting Observation – Query Hint – FORCE ORDER

SQL Server never stops to amaze me. As regular readers of this blog already know that besides conducting corporate training, I work on large-scale projects on query optimizations and server tuning projects. In one of the recent projects, I have noticed that a Junior Database Developer used the query hint Force Order; when I asked for details, I found out that the basic concept was not properly understood by him.

Today, let us try to understand its working and the effect of this hint. Further, we will see the extent of difference in performance created by this one query hint. I also have one interesting question for all of you as well; I will give the answer in one of my later posts.

USE AdventureWorks
GO
SELECT COUNT(*) CountEmployee -- 290 Rows
FROM HumanResources.Employee
GO
SELECT COUNT(*) CountEmployeeAddress -- 290 Rows
FROM HumanResources.EmployeeAddress
GO
SELECT COUNT(*) CountEmployeeDepartmentHistory -- 296 Rows
FROM HumanResources.EmployeeDepartmentHistory
GO
SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeAddress ea ON ea.EmployeeID = e.EmployeeID
INNER JOIN HumanResources.EmployeeDepartmentHistory edh ON edh.EmployeeID = e.EmployeeID
GO
SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeAddress ea ON ea.EmployeeID = e.EmployeeID
INNER JOIN HumanResources.EmployeeDepartmentHistory edh ON edh.EmployeeID = e.EmployeeID
OPTION (FORCE ORDER)
GO

In above example, I have used three tables and their row count is listed as well. Employee and EmployeeAddress – both have same number rows, and EmployeeDepartmentHistory table has around 6 rows more than the other two tables. Now let us run the query without using OPTION (FORCE ORDER) and run it along with the query hint and check the execution plan. You will find a difference in the query cost.

We all accept that the table with least number of rows should be listed as the base table, and the same is done here. We have two such tables with the least number of rows, which are listed as base tables. Now before we further explain this, let us see the execution plan for the same.

forceorder SQL SERVER   Interesting Observation   Query Hint   FORCE ORDER

It is very clear from the above execution plan that when I order is forced the query cost goes high. This proves that the SQL Server has already made a good decision with regard to the optimized query plan. When plan is forced in the case of the joins more than 2 table the performance matters. Let us see the execution order of the table in both the cases.

forceorder1 SQL SERVER   Interesting Observation   Query Hint   FORCE ORDER

Now, from the example, it is very clear when you force order the query, it evaluates the table Employee first and when it is not forced, it evaluates EmployeeAddress first. Even though both the tables have same number of rows, the query optimizer processes them differently and uses different types of join logic. When the order is not forced, it uses hash join; further, in case of forced order, it uses nested loop – this creates a significant difference in the query cost.

The conclusion of this whole exercise is very simple.

  • SQL Server Query Execution Engine is pretty smart to decide the best execution plan with least query cost for any query.
  • Order of the tables in any query can make a significant impact on the query.

Now the question for you: We have seen that using query hint of OPTION (FORCE ORDER) reduces the performance; give an example wherein we can use this hint to improve the performance?

Please leave your comment here. I will publish the answer to this question with due credit and with my own example in a later post.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Outer Join in Indexed View – Question to Readers

Today I have question for you. Just a day ago I was reading whitepaper Improving Performance with SQL Server 2008 Indexed Views. Following is question and answer I read in the white paper.

Q. Why can’t I use OUTER JOIN in an indexed view?

A. Rows can logically disappear from an indexed view based on OUTER JOIN when you insert data into a base table. This makes incrementally updating OUTER JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.

Here I would like to ask you one question, do you have example for kind of OUTER JOIN where you insert data into base table, it will make a row disappear from query?

Please post your answer as comment.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL Server – Multiple CTE in One SELECT Statement Query

I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject.

Option 1 :

/* Method 1 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT 2 AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

Option 2:

/* Method 2 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

Please note the difference between options. If there is any other option than above mentioned two options, please leave your comment here.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL Server – Understanding Table Hints with Examples

Today we have a very interesting subject to look at. I tried to look for help online but have not found any other documentation besides what we have from the Book Online.

Let us try to understand what are the different kinds of hints available in SQL Server and how they are helpful.

What is a Hint?

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.

Before we continue to explore this subject, we need to consider one very important fact and say some words of caution. SQL Server Query optimizer is a very smart tool and it makes a best selection of execution plan. Suggesting hints to the Query Optimizer should be attempted when absolutely necessary and by experienced developers who know exactly what they are doing (or in development as a way to experiment and learn).

There are three different kinds of hints. Let us understand the basics of each of them separately.

Please continue reading article here.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Introduction to JOINs – Basic of JOINs

The launch of Gandhinagar SQL Server User Group was a tremendous, astonishing success! It was overwhelming to see a large gathering of enthusiasts looking up to me (I was the Key Speaker) eager to enhance their knowledge and participate in some brainstorming discussions. Some members of User Group had requested me to write a simple article on JOINS elucidating its different types.

Download SQL Script used throughout in this article to practice along. Now, take a quick look at the following two tables I have created.

INNER JOIN

This join returns rows when there is at least one match in both the tables.
inner join SQL SERVER   Introduction to JOINs   Basic of JOINs

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
left join SQL SERVER   Introduction to JOINs   Basic of JOINs

RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
right join SQL SERVER   Introduction to JOINs   Basic of JOINs

FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.
outer join SQL SERVER   Introduction to JOINs   Basic of JOINs

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

cross join   half SQL SERVER   Introduction to JOINs   Basic of JOINs

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO

The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.

/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

left join null SQL SERVER   Introduction to JOINs   Basic of JOINs

The above example can also be created using Right Outer Join.

right join null SQL SERVER   Introduction to JOINs   Basic of JOINs

NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.

outer join null SQL SERVER   Introduction to JOINs   Basic of JOINs

You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.

USE AdventureWorks
GO
CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
CREATE TABLE table2
(ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
USE AdventureWorks
GO
/* INNER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* RIGHT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* OUTER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO
/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
/* CROSS JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
CROSS JOIN Table2 t2
GO
DROP TABLE table1
DROP TABLE table2
GO

I hope this article fulfills its purpose. I would like to have feedback from my blog readers. Please suggest me where do you all want me to take this article next.

Click to Download Scripts

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Logical Query Processing Phases – Order of Statement Execution

You can download the poster from Itzik Ben-Gan’s T-SQL Querying page over here.

Of late, I penned down an article – SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN – which received a very intriguing comment from one of my regular blog readers Craig. According to him this phenomenon happens due to Logical Query Processing. His comment instigated a question in my mind. I have put forth this question to all my readers at the end of the article. Let me first give you an introduction to Logical Query Processing Phase.

What actually sets SQL Server apart from other programming languages is the way SQL Server processes its code. Generally, most programming languages process statement from top to bottom. By contrast, SQL Server processes them in a unique order which is known as Logical Query Processing Phase. These phases generate a series of virtual tables with each virtual table feeding into the next phase (virtual tables not viewable). These phases and their orders are given as follows:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

As OUTER join is applied subsequent to ON clause, all rows eliminated by the ON clause will still be included by the OUTER join as described in the article SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN.

However, I am perplexed about the last two, ORDER BY and TOP. According to some people TOP comes first in logical query processing phase while others suggest that ORDER BY comes first. Now, here I’ve laid down my questions for you all to think about:

1) What is the correct answer for order query processing phase – ORDER BY or TOP?
2) How can we create an example to verify query processing phase for ORDER BY and TOP?

I will soon publish the answers I receive to the above questions on this blog, with due credit given to my readers.

Reference : Pinal Dave (http://blog.SQLAuthority.com)