Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2) ... IF (CONDITION 3) AND (CONDITION 4) ...
CONDITION 1 is
CONDITION 2 be checked?
CONDITION 3 is
CONDITION 4 be checked?
What about conditions on
WHERE: does the SQL Server engine optimize all conditions in a
WHERE clause? Should programmers place conditions in the right order to be sure that the SQL Server optimizer resolves it in the right manner?
Thank to Jack for link, surprise from t-sql code:
IF 1/0 = 1 OR 1 = 1 SELECT 'True' AS result ELSE SELECT 'False' AS result IF 1/0 = 1 AND 1 = 0 SELECT 'True' AS result ELSE SELECT 'False' AS result
There is not raise a Divide by zero exception in this case.
If C++/C#/VB has short-circuiting why can't SQL Server have it?
To truly answer this let's take a look at how both work with conditions. C++/C#/VB all have short circuiting defined in the language specifications to speed up code execution. Why bother evaluating N OR conditions when the first one is already true or M AND conditions when the first one is already false.
We as developers have to be aware that SQL Server works differently. It is a cost based system. To get the optimal execution plan for our query the query processor has to evaluate every where condition and assign it a cost. These costs are then evaluated as a whole to form a threshold that must be lower than the defined threshold SQL Server has for a good plan. If the cost is lower than the defined threshold the plan is used, if not the whole process is repeated again with a different mix of condition costs. Cost here is either a scan or a seek or a merge join or a hash join etc... Because of this the short-circuiting as is available in C++/C#/VB simply isn't possible. You might think that forcing use of index on a column counts as short circuiting but it doesn't. It only forces the use of that index and with that shortens the list of possible execution plans. The system is still cost based.
As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.