One of my blog reader have asked a query on SQL Server query execution order. Thought of writing an article on the same.
select * from Student where id< 5000 group by studentID having
subjectid=10 order by student name
|
Here is the order of execution for your select statement.
FROM --; Which Primary table
ON --; On which column to join
JOIN --;With which table, you want to join
WHERE --; what are
the conditions to filter the record
GROUP BY --; on what basis, you want
to group.
WITH CUBE or WITH ROLLUP --; Show the data in the form of
knowledge cubes.
HAVING --; Another filter criteria
SELECT --;
Get the data
DISTINCT --; Remove duplicates
ORDER BY --; Display
in this order
TOP --; Display only this much.
|
That's really awesome processing of your query. My only concern is, why we are grouping the data afterwards we are having another filter criteria like having. May be having can be placed front before grouping it.
Anyway, I will put this question to Microsoft.
For our example,
select * from Student where id < 5000 group by studentID having
subjectid=10 order by student name
|
The order of execution is,
from --;where ---; group by --;having -- select -- Order by
|
Cheers,
Venkatesan prabu .J
http://venkattechnicalblog.blogspot.com/
http://www.kaashivinfotech.com/