SQL Short-circuit

This post looks at short-circuiting in SQL Server 2000/2005 and its caveats.

Consider a simple product search scenario. If the user enters something in the search box, you want to retrieve product results pertaining to the search text, otherwise you want to bring in all the products, effectively ignoring the parameter.Ideally you will page the results.

This is a typical example of Optional Parameters, requiring a Conditional Where Clause in your translation to SQL.

A simple SQL statement fails to capture this essential part of the problem domain and there seems to be no easy way to accomplish this. Most of the developers resort to using dynamic SQL, table variables with joins, If expressions or CASE Statements to accomplish this. Short-circuiting can come in handy in these situations and gives a performance boost that is worth investigating.

To illustrate that SQL does indeed support this feature, execute the statement below as indicated by Mark Cohen on his blog.

Select 1 Where 1=1 or 1/0=0

We indeed don't get a divide by zero exception reinforcing our claim that SQL Server does have short-circuiting support.

As Jeff points out, many of the CASE expressions can be converted into boolean logic and hence take advantage of short-circuiting.

Assume @CustomerID = -1 is the default value, indicating that nothing was passed in. Optional Parameters would generally be coded as one of these

Exec sp_executesql @YourDynamicStatement

(Or)

If @CustomerID = -1
Select * from Sales.Customer
Else
Select * from Sales.Customer Where CustomerID = @CustomerID

(Or)

Select * from Customer
Where
Case @CustomerID
When -1 Then 1
Else
Case When @CustomerID = CustomerID Then 1 Else 0 End
End = 1

(Or)

Select * from Customer
Where CustomerID =
Case
When @CustomerID = -1 Then CustomerID
Else @CustomerID
End

The equivalent boolean logic(with short-circuit) would be as below Where (@CustomerID = -1 or CustomerID = @CustomerID)

When @CustomerID = -1 , indicating that nothing is passed in, the right side expression is never evaluated.

As you can see, this is easily readable as well as maintainable.

Now if you look at the comments in Jeff's blog, a user complains that his short circuit doesn't work.

Select * from Northwind..Orders
Where CustomerID = CustomerID and OrderID > 1/(0*year(getdate()))

You would expect this statement to not generate a Divide By Zero exception, but it does. So Whats wrong here.

We found out, the short circuit works only if the expression is DETERMINISTIC. That is, if the engine can look at the expression and determine its truth value without having to run the query, then the engine short-circuits the statement, effectively ignoring the entire expression.

Eg.; The truth value of @CustomerId = -1 can be determined before hand and hence is deterministic.Similary are

Select 1 Where getdate()=getdate() and 1/0=0

Select 1 Where 1=1 or 1/0=0

Select 1 Where 1=1/0 or 1=1

So as long as the expression is deterministic (truth value can be determined), you can take advantage of short-circuiting.

Though CustomerID = CustomerID seems deterministic in a fleeting glance, it is NOT DETERMINISTIC because in SQL by default null IS NOT EQUAL to null. So the engine cannot determine before hand the value of the left side expression and hence cannot short-circuit and fails.

So the next time you are doing Conditional Where clauses, convert the condition into boolean logic and take advantage of short-circuiting built into SQL Server.