Rewriting OR Conditions So the Index Gets Used

Both search columns have indexes, yet a query that joins them with OR can still end up scanning the table. OR conditions across those columns give the optimizer a harder access-path decision than one simple equality.

Build a Search With OR Conditions on Two Columns

An equality predicate on one indexed column has a straightforward route. Searching either of two columns requires combining qualifying rows. SQL Server can choose separate access paths and remove overlaps. It can also decide that a single scan costs less. Neither outcome follows from index existence alone.

I inspect selectivity and selected columns before rewriting the predicate. Returning a wide row adds lookup work after a narrow seek. If either branch matches much of the table, a scan deserves consideration. An index does not issue a binding invitation to the optimizer.

Run this setup in one SSMS session. GENERATE_SERIES requires SQL Server 2022 or later and compatibility level 160 or higher. The chosen inputs create overlapping matches, unmatched rows, and NULL values. They let you test correctness alongside the plan.

CREATE TABLE #SearchRows
(
    SearchID int NOT NULL PRIMARY KEY,
    CustomerID int NULL,
    RegionCode varchar(8) NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #SearchRows (SearchID, CustomerID, RegionCode, Amount)
SELECT value,
       CASE WHEN value % 17 = 0 THEN NULL ELSE value % 500 END,
       CASE WHEN value % 37 = 0 THEN 'WEST' ELSE 'OTHER' END,
       CAST(value % 100 AS decimal(12,2))
FROM GENERATE_SERIES(1, 20000);
CREATE INDEX IX_SearchRows_Customer
ON #SearchRows(CustomerID) INCLUDE (RegionCode, Amount);
CREATE INDEX IX_SearchRows_Region
ON #SearchRows(RegionCode) INCLUDE (CustomerID, Amount);

Read the Original Plan First

Enable the actual execution plan with Ctrl+M in SSMS. Run the original statement with STATISTICS IO enabled. Record logical reads for the table and any worktables. Inspect seeks, scans, residual predicates, and lookup operators. The graphical percentage alone cannot explain how much data was read.

SET STATISTICS IO ON;
SELECT SearchID, CustomerID, RegionCode, Amount
FROM #SearchRows
WHERE CustomerID = 12 OR RegionCode = 'WEST';
SET STATISTICS IO OFF;

On my SQL Server 2025 test instance, this original query already used both indexes. The plan showed two index seeks, a Merge Join (Concatenation) operator, and a Stream Aggregate that removes the overlap. That is the automatic index union described later, so this sample has nothing to fix yet. The rewrites below show the pattern for the day your plan chooses a scan instead.

This statement returns a qualifying base row once, even when both conditions match. Preserve that behavior during the rewrite. Compare row identifiers as well as counts. Equal counts can hide one missing row and one accidental duplicate. A fast answer still needs to be the right answer.

Split OR Conditions With UNION

UNION lets each branch express a simple equality predicate. The optimizer can cost those branches separately. A distinct operation then removes repeated result rows. Keep the unique SearchID in the projection so duplicate removal represents overlapping source rows rather than identical-looking business values.

SET STATISTICS IO ON;
SELECT SearchID, CustomerID, RegionCode, Amount
FROM #SearchRows
WHERE CustomerID = 12
UNION
SELECT SearchID, CustomerID, RegionCode, Amount
FROM #SearchRows
WHERE RegionCode = 'WEST';
SET STATISTICS IO OFF;

Without that unique identifier, UNION can collapse different base rows with equal projected values. The original predicate preserves both rows. This matters in reports that intentionally omit the primary key. Validate the complete relational behavior before removing columns to make an example look tidy.

Make the Branches Disjoint

UNION ALL avoids global duplicate removal, but a row matching both branches appears twice. Exclude the first branch's matches from the second branch. The exclusion also needs explicit NULL handling. CustomerID <> 12 alone evaluates to unknown for a NULL customer, which loses valid region matches.

SET STATISTICS IO ON;
SELECT SearchID, CustomerID, RegionCode, Amount
FROM #SearchRows
WHERE CustomerID = 12
UNION ALL
SELECT SearchID, CustomerID, RegionCode, Amount
FROM #SearchRows
WHERE RegionCode = 'WEST'
  AND (CustomerID <> 12 OR CustomerID IS NULL);
SET STATISTICS IO OFF;

For more complicated OR conditions, the exclusion grows harder to reason about. Write a truth table for overlap and NULL cases. Keep all output expressions identical across branches. If the logic becomes fragile, pay the distinct cost and choose the clearer statement. Maintenance belongs in the performance discussion too.

Prove the Row Sets Match

I check the rewritten results before comparing speed. Save the original and rewritten identifiers into separate temporary tables. The following test checks missing and extra identifiers with EXCEPT, then checks repeated identifiers independently. Both checks matter because EXCEPT itself removes duplicates.

SELECT SearchID INTO #OriginalMatches
FROM #SearchRows
WHERE CustomerID = 12 OR RegionCode = 'WEST';
SELECT SearchID INTO #SplitMatches
FROM #SearchRows WHERE CustomerID = 12
UNION ALL
SELECT SearchID FROM #SearchRows
WHERE RegionCode = 'WEST'
  AND (CustomerID <> 12 OR CustomerID IS NULL);
SELECT SearchID FROM #OriginalMatches
EXCEPT
SELECT SearchID FROM #SplitMatches;
SELECT SearchID FROM #SplitMatches
EXCEPT
SELECT SearchID FROM #OriginalMatches;
SELECT SearchID, COUNT_BIG(*) AS Occurrences
FROM #SplitMatches
GROUP BY SearchID
HAVING COUNT_BIG(*) > 1;

Repeat these checks with representative application predicates. Include rows matching both branches, only one branch, neither branch, and nullable columns. For joins, preserve their multiplicity too. A base-table identifier test is insufficient when the original query deliberately returns multiple joined rows per identifier.

Handle the Optional Parameter Separately

A different pattern uses NULL to mean every customer. One reusable plan has to serve both a selective search and an unfiltered result. Recompilation lets SQL Server simplify the expression for each execution's actual value. It also adds compilation work, which matters for frequently executed small statements.

DECLARE @CustomerID int = 12;
SELECT SearchID, CustomerID, RegionCode, Amount
FROM #SearchRows
WHERE @CustomerID IS NULL OR CustomerID = @CustomerID
OPTION (RECOMPILE);

Test with NULL and representative non-NULL values. OPTION (RECOMPILE) is a statement-level choice here. SQL Server 2025 adds optional parameter plan optimization under compatibility level 170, controlled by the OPTIONAL_PARAMETER_OPTIMIZATION database scoped configuration. That setting was on by default in the test database. Eligible parameterized statements get variants without your hand-written split. Local-variable examples do not establish that eligibility.

Recognize Automatic Index Union Plans for OR Conditions

SQL Server can produce an index union plan for OR conditions by combining matches from separate indexes. That plan includes overlap handling where required. You do not always need to write UNION yourself, and the original query in this post got that plan without any help. Review the plan already selected before adding a permanent rewrite.

Index intersection combines identifiers that satisfy predicates on both indexed columns. It fits AND logic, rather than the union of matches required by this search. A plan with several seeks is not automatically an intersection. Read the join, concatenation, and duplicate-removal operators to understand which set operation the optimizer implements.

Keep the Winner Across Parameter Values

Which parameter makes your rewrite stop being selective? Test that value as well as the convenient example. Compare logical reads, returned identifiers, lookup work, memory use, and compilation cost. Include the widest result projection your application really needs. Covering indexes in this example intentionally simplify the access paths.

Adopt the form that preserves behavior and performs well across the supported workload. Keep the original statement and recorded comparison available.

Repeat each candidate with warmed caches and the same session settings. Logical reads describe buffer access even when pages are already in memory. Physical reads depend on cache state and storage activity. Do not clear shared production caches to manufacture a cleaner comparison. Use an isolated test environment when cold-cache behavior matters. Also apply the same ORDER BY to both candidates when the application requires sorted output. A UNION operation provides duplicate handling, but it does not promise display order. Sorting costs belong in the full comparison.

Test joins and pagination around the rewritten statement, since those surrounding operations can change the optimizer's choices. If the optimizer already combines the indexes effectively, extra branches add code without solving a problem. Let actual plans and your own measurements decide.

Related reading on this blog: UNION vs UNION ALL: Reading the Operators Each One Adds to a Plan and Performance: OR vs IN.

A predicate rewrite is not proof of a faster query, it is another access path to test.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.