Simple SQL Questions

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
OPTION (FAST 5)

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.


LVL 3
shekhar_shashiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JestersGrindCommented:
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.

Greg

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shekhar_shashiAuthor Commented:
Thanks. Where does 'Top 5' lie in the picture?
0
JestersGrindCommented:
I forgot to include that in the list, but TOP is actually last.

Greg

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LowfatspreadCommented:
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?
0
shekhar_shashiAuthor Commented:
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?
0
LowfatspreadCommented:
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...)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
WCF

From novice to tech pro — start learning today.