Do all parts of a SQL SERVER expression using 'OR' get evaluated?

Question

Given:

WHERE (@Id Is NULL OR @Id = Table.Id)

If @Id is null: the expression evaluates to true. Does the second part @Id = Table.Id still get considered? or is it sufficient that the expression evaluates to true given that the first part is (which is the case in c#).

This is relevant because of some much more complex OR statements where it is important to know if all parts are getting evaluated.

---

UPDATE:

I have since found this syntax to be a good alternative

WHERE (Table.Id = ISNULL(@Id, Table.Id))

Eastbound

In complicated queries both sides might be run in parallel... – Topside

Answer 1

Sometimes they are, sometimes they aren't. SQL Server does not guarantee boolean operator short circuit, don't rely on it for correctness. See this blog entry: Boolean Operator Short Circuit.

Complex queries that depend on @variables like this are much better written as explicit IF statements:

IF (@id IS NULL)
  SELECT ... FROM ... WHERE ...
ELSE
  SELECT ... FROM ... WHERE ...

Eagan

Answer 2

Execution plans may not be so great with a query like that. Both will be evaluated.

Etty

I ran the example from the blog Remus posted, "select 'Will not divide by zero!' where (@id is NULL or 1/0=0)" did not cause an error when @id is NULL – Eastbound