What is the order of execution for this SQL statement

Question

I have below SQL Query :

SELECT TOP 5 C.CustomerID,C.CustomerName,C.CustomerSalary
FROM Customer C
WHERE C.CustomerSalary > 10000
ORDER BY C.CustomerSalary DESC

What will be execution order of the following with proper explanation ?

1. TOP Clause
2. WHERE Clause
3. ORDER BY Clause

Answer 1

Check out the documentation for the SELECT statement, in particular this section:

Logical Processing Order of the SELECT statement

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

which gives the following order:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

Answer 2

1. WHERE
2. ORDER BY
3. TOP

Here is a good article about that: http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

Answer 3

Simply remember this phrase:- Fred Jones' Weird Grave Has Several Dull Owls

Take the first letter of each word, and you get this:-

FROM
(ON)
JOIN
WHERE
GROUP BY
(WITH CUBE or WITH ROLLUP)
HAVING
SELECT
DISTINCT
ORDER BY
TOP

Hope that helps.

Answer 4

This is exact execution order, with your case.

1-FROM
2-WHERE
3-SELECT 
4-ORDER BY
5-TOP

Answer 5

TOP, WHERE, and ORDER BY are not "executed" - they simply describe the desired result and the database query optimizer determines (hopefully) the best plan for the actual execution. The separation between "declaring the desired result" and how it is physically achieved is what makes SQL a "declarative" language.

Assuming there is an index on CustomerSalary, and the table is not clustered, your query will likely be executed as an index seek + table heap access, as illustrated in this SQL Fiddle (click on View Execution Plan at the bottom):

As you can see, first the correct CustomerSalary value is found through the Index Seek, then the row that value belongs to is retrieved from the table heap through RID Lookup (Row ID Lookup). The Top is just for show here (and has 0% cost), as is the Nested Loops for that matter - the starting index seek will return (at most) one row in any case. The whole query is rather efficient and will likely cost only a few I/O operations.

If the table is clustered, you'll likely have another index seek instead of the table heap access, as illustrated in this SQL Fiddle (note the lack of NONCLUSTERED keyword in the DDL SQL):

But beware: I was lucky this time to get the "right" execution plan. The query optimizer might have chosen a full table scan, which is sometimes actually faster on small tables. When analyzing query plans, always try to do that on realistic amounts of data!

Answer 6

Visit https://msdn.microsoft.com/en-us/library/ms189499.aspx for a better explanation.

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

FROM

ON

JOIN

WHERE

GROUP BY

WITH CUBE or WITH ROLLUP

HAVING

SELECT

DISTINCT

ORDER BY

TOP

Answer 7

My $0,02 here.

There's two different concepts in action here: the logical execution order and the plan of query execution. An other was to see it is who answers the following questions:

1. How MSSQL understood my SQL Query?
2. What it'll do to execute it in the best possible way given the current schema and data?

The first question is answered by the logical execution order. Brian's answer show what it is. It's the way SQL understood your command: "FROM Customer table (aliased as C) consider only the rows WHERE the C.CustomerSalary > 10000, ORDER them BY C.CustomerSalary in descendent order and SELECT the columns listed for the TOP 5 rows". The resultset will obey that meaning

The second question's answer is the query execution plan - and it depends on your schema (table definitions, selectivity of data, quantity of rows in the customer table, defined indexes, etc) since is heavily dependant of SQL Server optimizer internal workings.

Answer 8

Here is the complete sequence for sql server :

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

So from the above list, you can easily understand the execution sequence of TOP, WHERE and ORDER BY which is :

1.  WHERE
2.  ORDER BY
3.  TOP

Get more information about it from Microsoft