Inside Microsoft® SQL Server® 2008: T-SQL Querying

Logical Transformations

In several solutions I’ve presented, I used logical expressions with an OR operator to deal with precedence based on multiple attributes. Such was the case in the recent solutions for paging, matching current and previous occurrences, and other problems. I used OR logic because this is how human minds are accustomed to thinking. The logical expressions using OR logic are fairly intuitive for the purpose of determining precedence and identifying rows that follow a certain anchor.

However, because of the way SQL Server’s optimizer works, OR logic is problematic in terms of performance, especially when some of the filtered columns are not indexed. For example, consider a filter such as col1 = 5 OR col2 = 10. If you have individual ...