GSquared (2/28/2008)
Good article.One small quibble with it. In one of the examples, you use "((FullName1 = 'Ed Jones') OR (FullName1 IS NULL))", as an example of how to get Index Seeks, instead of Scans.
In many cases, the moment you put an "or" in a Where clause, you get a scan instead of a seek. Not always, but it is something to watch out for. The same applies for "in ()" (since that's just a way to shorthand multiple "or" statements).
If you really HAVE to do the OR clause, I've found an approach like this works best, to avoid scans as much as possible. For example, if you have one of those stinking reports where the users have demanded 15 different parameters, but they don't know which one they'll use so you have to stack up NULL checks as well as column comparisons:
SELECT...
FROM....
WHERE
CASE WHEN @MyParam IS NULL THEN 1
WHEN @MyParam = table1.Column1 THEN 1
ELSE 0
END = 1
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning