Suppose you want to return all the rows from T1, where the result of col1 divided by col2 is greater than zero. For this discussion, suppose you prefer not to return rows that have a zero value in col2, in which the computation of col1 divided by col2 would result in omega (eternity). The following query results in a divide by zero error:
SELECT * FROM T1
WHERE (col1 / col2 > 0)
Note that before the error occurs, SQL Server processes and returns one row. As soon as the error occurs, the query breaks and SQL Server processes no further rows. Let's slightly revise the query:
SELECT * FROM T1
WHERE (col2 0)
AND (col1 / col2 > 0)
When I ran this query on my machine with SQL Server 7.0 and Service Pack 2 (SP2), the query ran successfully, returning two rows. Let's examine the execution plan output for the query:
|--Filter(WHERE:([T1].[col2]0 AND
[T1].[col1]/[T1].[col2]>0)
|--Table Scan(OBJECT:([testdb].[dbo].[T1]))
The system scanned the table and performed a filter to limit the result set according to the WHERE clause's filter. Notice that in this case, the filter in the execution plan appears to be the same from the query. Running the same query on my SQL Server 2000 beta 2 installation returned the same results, but the execution plan looks slightly different:
|--Table Scan(OBJECT:([testdb].[dbo].[T1]),
WHERE:([T1].[col2]0 AND
[T1].[col1]/[T1].[col2]>0))
The query plan shows the scan and the filter in one operation, but the logical expression still looks the same. Let's rewrite the query, flipping the left and the right parts of the logical expression, then execute the query:
SELECT * FROM T1
WHERE (col1 / col2 > 0)
AND (col2 0)
On my SQL Server 7.0 machine, I get one row and a divide by zero error. Let's look at the execution plan:
|--Filter(WHERE:([T1].[col1]/[T1].[col2]>0 AND
[T1].[col2]0))
|--Table Scan(OBJECT:([testdb].[dbo].[T1]))
Notice that the expression in the filter appears to be the same as the expression in the query. Because the system evaluates the left part of the expression (col1 divided by col2 > 0) first, as soon as the system processes the row with the value 0 in col2, the query aborts. However, I ran this query successfully on SQL Server 2000. Let's look at the execution plan.
|--Table Scan(OBJECT:([testdb].[dbo].[T1]),
WHERE:([T1].[col2]0 AND
[T1].[col1]/[T1].[col2]>0))
Notice that the optimizer rewrote the expression and used the same expression from the first query. In any case, the queries that ran successfully would have failed if SQL Server didn't support short circuit. However, be aware of two aspects of how SQL Server's short circuit feature works. First, SQL Server will short-circuit an expression if it determines that the expression evaluates to either TRUE or FALSE. Second, there's no query hint you can apply to force the optimizer to use the exact expression you wrote so that the expression will appear the same way in the plan that SQL Server builds. Although in the previous example, the optimizer revised the logical expression col1/col2 AND col2>0 to col2>0 AND col1/col2, you have no guarantee that it wouldn't do the opposite when you use the form col2>0 AND col1/col2.
You can also try using one of the variations I present in Figure 1 and Figure 2 to rewrite your query as a derived table. On my SQL Server 7.0 machine, Query 1, which callout A in Figure 1 shows, ran successfully. Query 2, which callout A in Figure 2 shows, failed. But can you guarantee that such queries will always behave this way? Look at the execution plans in each figure. Notice that in both cases the optimizer combined the two simple expressions to form one complex expression. Again, you can't guarantee the order, even though it looks as if you can. Running two different queries in SQL Server 2000 resulted in the same execution plans, as Figure 1 shows for Query 1 and Figure 2 shows for Query 2. Notice that the optimizer revised the expression in Query 2, and the execution plans for both queries produced the same result; they both ran successfully, as callout C in Figure 1 and callout C in Figure 2 show.
If you want to guarantee a certain order of processing in an expression, you need to use a CASE expression. Let's revise our query:
SELECT * FROM T1
WHERE
CASE
WHEN col2 = 0 THEN 0
WHEN col1 / col2 > 0 THEN 1
ELSE 0
END = 1
CASE is by nature a short circuit, which lets you control the processing order. SQL Server always evaluates CASE expressions from top to bottom, and when any of the expressions evaluates to TRUE, SQL Server doesn't process any further expressions.
I've demonstrated that the optimizer might revise the logical expressions you write. Therefore, you can't count on this feature when you write your queries. But for performance considerations, you benefit from knowing that SQL Server internally supports the short circuit feature, and that this feature saves many CPU cycles. In many cases, you can rewrite logical expressions to use a CASE expression instead, guaranteeing a short circuit.
This article is adapted from Advanced Transact-SQL for SQL Server 2000 (Apress), by Itzik Ben-Gan and Tom Moreau.