I have a query that does a simple search on the "Person" table.
Select Top 5 *
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.