Logical processing order (a.k.a. binding order) of a SELECT query in Microsoft SQL Server T-SQL is fundamental.  This is because it has direct bearing on what results (data) you get, which is the whole reason for querying in the first place.  Unfortunately for us, it looks almost nothing like the order we have to remember to use when writing T-SQL syntax.  As of Microsoft SQL Server 2014, logical processing order goes like this:

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

If there is some meta relationship betwixt syntactical order and logical processing order, I haven’t yet heard one that makes sense to me.  So, like many things, it seems there is no avoiding just having to remember it.  Abbreviating slightly, we get the acronym:

F O J W G W H S D O T

A simple mnemonic for remembering it:

“FRESH orange juice will go with hot sauce, donuts, OR toast!”

Now, according to Microsoft, “Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.”  “Blast!” we might think to ourselves.  Well, however things get done physically, and whatever combination of iterators is produced for the execution plan, and whatever the performance ramifications… at least we can produce the results we want (1). Which is nice.

And so we live to fight another day!

(1) …Now that we have a better idea of why we can’t select a field/attribute/column that isn’t in the GROUP BY (i.e., “Msg 8120, Level 16, State 1 Column xyz is invalid in the select list”)… because SELECT comes after GROUP BY!

Leave a Reply

Your email address will not be published. Required fields are marked *