Is SQL Server 2012 predicate precedence overridden by the all-at-once concept?
I have been reading Querying Microsoft SQL Server 2012 by Itzik Ben-Gan, Dejan Sarka, and Ron Talmage. Chapter 3 lesson 1 Page 67 describes predicate precedence. Towards the bottom of the page it is written that the SQL Server all-at-once concept will override predicate precedence based on cost.
Below is a cut and paste from the book in PDF format:
The reality, though, is different. SQL Server does internally support a short-circuit concept; however, due to the all-at-once concept in the language, it is not necessarily going to evaluate the expressions in left-to-right order. It could decide, based on cost-related reasons, to start with the second expression, and then if the second expression evaluates to true, to evaluate the first expression as well.
If this is correct then predicate precedence as described in this book and an article by Itzik Ben-Gan that he posted to SQLmag http://sqlmag.com/t-sql/short-circuit are irrelevant. The all-at-once concept will always choose the predicate precedence.
I believe that if the expressions are not evaluated according to precedence, left to right, then precedence would in fact be superseded by the all-at-once concept.
Best Answer
I believe I understand what the authors are trying to teach now (thanks to everyone's comments). I believe they are stating that if I am writing SQL that contains logic that depends on short-circuiting then the logic might be flawed due to the all-at-once concept which will evaluate predicates based on cost and not by order of precedence. So, the answer to this question is yes. The all-at-once concept does override predicate precedence.
I am reading the book right now in preparation for the 70-461 exam and I wanted to confirm with other writers of SQL if I truly did not have control over the order in which the predicates of my SQL statements are evaluated. It turns out that I do not. A
CASEdoes not even guarantee the order according to this Connect item: Aggregates Don't Follow the Semantics OfCASE.The Microsoft Technet article All-at-Once Operations in T-SQL by Saeid Hasani provides a more detailed explanation of the all-at-once operations concept. The Technet article does not state that the cost of a predicate is the reason why some predicates are evaluated before others which is the reason given in the book Querying Microsoft SQL Server 2012.