I did some limited hallway testing today and discovered that a fair number of people do not know that SQL Server does short-circuit evaluation. For those who don’t know, the simple explanation is that SQL Server will evaluate the left hand side of a conditional statement with an AND in the middle and if it is false the SQL will not bother with the right.
To demonstrate, try this:
select ‘short circuit’ where 1=0 and 1/0 = 0
If SQL supports short circuit evaluation nothing would be returned – obviously 1 is never equal to 0
If SQL evaluated both sides, this would cause an error.
The implications of this on search procedures is simple yet massive. If a where clause has to check a value or ignore the check if the parameter is null, most people write this as
table.field = @parameter OR @parameter IS NULL
If there are enough parameters, this will quickly lead to performance problems. SQL will do the left comparison regardless of the value of the parameter for every parameter, for every row in the table. Many people will resort to dynamic SQL queries to work around this. Here’s another take: Use short-circuit evaluation:
NOT (@parameter IS NOT NULL AND table.field <> @parameter )
The left hand side will fail before the right hand side is even evaluated, and because the left hand side can be determined before the query runs, SQL is smart enough to evaluateit only once. The performance difference is worth investigating.