on using order in subqueries error thrown: e.g.:
set rowcount 10 select * xxx col1 in( select col2 yyy order col3 desc ) wanted understand why type of queries restricted in sql-server? error thrown was:
the order clause invalid in views, inline functions, derived tables, subqueries, , common table expressions, unless top, offset or xml specified.
why?
short answer:
because sets have no order.
longer answer:
sql implementation of relational calculus, based on tuple-sets (tables, rowsets, etc.). sets not have order (unlike related concept, lists sets ordering).
further, there's (generally) no benefit applying ordering set before final output, , considerable amount of harm, because:
- the optimizer can add or remove ordering @ stage of query execution, sees fit, increase performance of query,
- so, (considerable) work,
- and optimizer might undo next step anyway
- and queries , stages can done in parallel, optimizer sees fits, , in conflict preserving ordering.
- so applying earlier, doesn't mean that order still there in later stage
the place commonly makes sense in final output, because data has transmitted serially there anyway.
the places makes sense before final output things top (n) needs ordering determine rows "top" rows.
it can make sense aggregate functions, why many of them have own order by clauses. constructing xml results in intermediate stages.
Comments
Post a Comment