All of the indexing strategy posts I’ve written in the past have been concerned with predicates combined with ANDs. That’s only one half of the possibilities though. There’s the case of predicates combines with ORs, and the guidelines for indexing that work well with ANDs don’t work with ORs
When dealing with predicates combined with AND, the predicates are cumulative, each one operates to further reduce the resultset.
For this reason, multi-column indexes support multiple predicates combined with AND operators.
If we look at a quick example, consider the following.
CREATE TABLE Customers (
CustomerID INT IDENTITY PRIMARY KEY,
Surname VARCHAR(30) NOT NULL,
CustomerType CHAR(1) NOT NULL,
IsActive BIT DEFAULT 1 NOT NULL,
RegistrationDate DATETIME NOT NULL DEFAULT GETDATE()
CREATE INDEX idx_Customers_SurnameFirstName ON Customers (Surname, FirstName);
Again I’m going to be lazy and get SQLDataGenerator to generate a few rows.
With that two column index on those columns and a query that looks for Surname = ‘Kelley’ AND Name = ‘Rick’, SQL can do a double column seek to go directly to the start of the range then just read down the index to the end of the range, basically until it finds the first row that it’s not interested in.
So how does that that differ when the operator is an OR?
The main difference is that with an OR, the predicates are independent. The second doesn’t serve to reduce the recordset, but rather to expand it. It’s similar to evaluating two separate predicates and combining the result. Let’s have a look at that 2 column index again when the two predicates are combined with an OR.
WHERE Surname = 'Kelley' OR FirstName = 'Rick';
If we try to use that index to evaluate Surname = ‘Kelley’ OR Name = ‘Rick’, there’s a problem. While the first of those predicates can be evaluated with a seek (it’s a sargable predicate on the left-most column of an index), the second predicate cannot. It’s sargable, but it is on the second column of the index (and for the moment let’s assume there are no other indexes on the table). Seeks are only possible if the predicate filters on a left-based subset of the index key.
Hence to evaluate that predicate SQL will have to do an index scan. Since it has to do a scan to evaluate the one predicate, it won’t bother also doing a seek to evaluate the first predicate as it can also evaluate that during the scan.
Hence, in this case, the query will execute with a single index scan.
So how do we get this query to rather seek?