SQL Server performance tip [duplicate]

Question

Possible Duplicate:
Is the SQL WHERE clause short-circuit evaluated?

I have the following question regarding this query:

select * from sometable 
where 1=1 or (select count(*) from table2 > 0)

If the first condition is true (1=1), does SQL Server still do the inner select? Or does it stop when the first condition is true (like C)

Answer 1

It stops when the (1=1) is true. You can check easily using Ctrl-M then Ctrl-E

Consider this query

select * from master..spt_values
where 1=1 or (select count(*) from master..sysobjects) > 0

The execution plan only shows a scan in master..spt_values and no activity in sysobjects.

Contrary to C, it does not stop when the LEFTMOST condition is true, instead the query optimizer works out independently of order presented which is the least cost to evaluate. In the case of the constants 1 vs 1 the winner is clear.

Answer 2

It is short circuited only because you are comparing literals. The optimiser detects this trivial comparison.

But if you have forced parameterisation set then the literals are changed to parameters and SQL Server works on a general case that each side could be different. Subject to the long list of conditions for forced parameterisation...