• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

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.


0
shekhar_shashi
Asked:
shekhar_shashi
  • 2
  • 2
  • 2
2 Solutions
 
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now