SQL Server performance tip [duplicate]

This question already has answers here: Closed 11 years ago.

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 ta开发者_StackOverflow中文版ble2 > 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)

---

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.

---

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...

---

As a general rule, SQL Server does not guarantee boolean operator short-circuit. Do not write queries that rely on boolean short-circuit for performance or correctness.

---

I will point out that you may get different results if you say where 1=1 or id = 10, I know I added the 1=1 to a query and got different results. I would not use 1=1 with an OR clause under any circumstances.