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.
精彩评论