One of the most important aspects in optimizing MSSQL queries is understanding the steps and order the query is processed since the query is processed in the order that is different than the code written. IMHO, this is the main difficulty in understanding complicated queries.
According to ANSI standard the query should be written in the following way:
SELECT [...] FROM [table1] JOIN [table2] ON [condition] WHERE [...] GROUP BY [...] HAVING [...] ORDER BY [...]
However the query will be processed by MSSQL in the following order like so:
FROM [table1] ON [condition] JOIN [table2] WHERE [...] GROUP BY [...] HAVING [...] SELECT [...] ORDER BY [...]
Please note, that ORDER BY is the last to be processed, only TOP clause would be processed after ORDER BY. DISTINCT will be processed after SELECT and before ORDER BY.
Saturday, June 30, 2007 9:29 PM