Simple SQL Questions

Posted on 2011-10-21
Last Modified: 2012-08-14
I have a query that does a simple search on the "Person" table.

Select Top 5 *
from Person
Where LastName = 'Doe'
Order by DateOfBirth

My questions are:
1. In what order do the different clauses in the query run?

I think the order is
a. Where clause on the entire table
b. Order By on the results obtained from a.
c. Top 5 * on the sorted result obtained from b.

The results seem to match my guess. Is my guess correct?
2. What do we gain by addiing an option fast to the query? For example,

select Top 5 * from Person
where LastName = 'Doe'
order by dob desc

I think the query execution is as follows:
a. Run where clause on the entire table
b. Run Order by on result from a.
c. run top 5

Option fast run runs in a separate thread and returns rows quickly as soon as the 5th record is retrieved by the query.

Is my understanding correct? I am running all the queries in SQL Server 2008 through SQL Server Management Studio.

Thanks in advance.

Question by:shekhar_shashi
    LVL 21

    Accepted Solution

    The order is:

    FROM clause
    WHERE clause
    GROUP BY clause
    HAVING clause
    SELECT clause
    ORDER BY clause

    Second, OPTION (FAST 5) does not make the whole query run any faster.  It returns 5 rows to the client as soon as they have been found, then continues with the remaining rows.  In your case it does nothing because you are only returning 5 records.


    LVL 3

    Author Comment

    Thanks. Where does 'Top 5' lie in the picture?
    LVL 21

    Expert Comment

    I forgot to include that in the list, but TOP is actually last.


    LVL 50

    Expert Comment

    the is no order that can be ascertained by just looking at a query as written in sql...

    your question is pretty meaningless actually... it is just done!

    if you want to see how the statement will be processed then you can look at the execution plan of the query to gain some idea
    as to how the query will normally be processed...

    the query is parsed by the database engine and using the catalog objects and statistics that the engine knows about the underlying data will determine the methods used to obtain it...

    depending on the associated database objects that exist for the tables (indexes, indexed views, partitions, order by clauses, top etc...) many different clauses present/not present in the query will have an effect on determining the access paths chosen...

    it is not TRUE to say the where clause DETERMINES the order of execution, often it can be the selected columns, or the required order (order by, top, over,..) which will be the main factor.

    what are you trying to understand... / do?
    LVL 3

    Author Comment

    1. So the purpose of top 5 is just to reduce the number of records that are returned to the client.  The database engine will find the best query execution path and complete the execution that would return xx records. These xx records will be sorted in the order specified in the query (if there is an order by clause). Once that is done, the top 5 records will be yanked out and returned to the client. IIs my understanding correct?

    2. The purpose of option fast is to return the records quickly. How does that work in the context of an order by clause? The database engine executes a query and gets xx records. Then it sorts the records if there is an order by clause. Does the option fast occur before order by is executed?
    LVL 50

    Assisted Solution

    1. basically correct , however if you don't specify an order by clause its possible that the dbms doesn't have to obtain more than your top number of rows anyway... consider filtered indexes....

    2.  the order by will always be taken into account , but that doesn't always mean a separate sort has to be performed (an index in the desired order may be available...) .. fast occurs last generally and returns those rows.. however the remaining rows may/will be processed in a more efficient manner and marshalled to you (buffers etc...)

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    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.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now