开发者

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

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:

开发者_C百科

I have since found this syntax to be a good alternative

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


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


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜