Indexes on individual search columns do not guarantee a good combined access path. Splitting OR conditions into separate branches can give each branch a useful access path. The rewrite must preserve overlap and NULL behavior before its execution plan deserves any attention.
Check the Existing Plan Before Splitting OR Conditions
SQL Server can optimize some OR predicates through index-union strategies. It can also choose a scan when the estimated result is broad or the lookup cost is high. The presence of separate indexes does not force two seeks or prove the optimizer missed an obvious plan.
I capture the original actual plan and IO before writing a rewrite. That makes the investigation about a concrete access problem. If the engine already combines useful index access, a more complex query can add maintenance burden without improving the workload.
The sample contains synthetic rows that match the first predicate, the second, both, and neither. It also includes a NULL in ColA. Keep a stable ID in the projection so the returned row identity is clear. Two predicates do not need two copies of the same row. UNION ALL is very efficient at taking that instruction literally if you forget the overlap rule.
When splitting OR conditions, preserve every qualifying source row while making the branches disjoint.
Build the Baseline With Compatible Parameters
The following setup creates two indexes supporting the individual search columns. Included columns cover the demonstrated projection. A tiny table can still favor a scan, so use the sample for correctness and representative data for the actual cost comparison.
The baseline predicate returns any row matching ColA equal to the first parameter or ColB equal to the second. SQL NULL comparisons follow three-valued logic, so a NULL parameter does not act as a wildcard. Preserve that contract unless the application explicitly defines another rule.
I use parameter types matching the columns. Implicit conversion can change access behavior and make a rewrite appear to solve the wrong problem. Keep the submitted application types, lengths, and settings with the production plan. A literal SSMS reproduction is useful only when it represents the actual request. Record the original statement and parameters before trying alternative forms.
CREATE TABLE #ORRows(ID int PRIMARY KEY,ColA int NULL,ColB int NULL,Payload varchar(40));
INSERT #ORRows VALUES(1,1,8,'A only'),(2,9,2,'B only'),(3,1,2,'Both'),
(4,NULL,2,'NULL A'),(5,9,9,'Neither');
CREATE INDEX IX_OR_A ON #ORRows(ColA) INCLUDE(ColB,Payload);
CREATE INDEX IX_OR_B ON #ORRows(ColB) INCLUDE(ColA,Payload);
DECLARE @A int=1,@B int=2;
SET STATISTICS IO ON;
SELECT ID,ColA,ColB,Payload FROM #ORRows WHERE ColA=@A OR ColB=@B;
SET STATISTICS IO OFF;Make the Second Branch Disjoint
The first branch returns ColA matches. The second returns ColB matches that were not already included by the first branch. For a non-NULL first parameter, that exclusion includes rows where ColA differs or is NULL. Without the NULL alternative, valid ColB matches with NULL ColA would disappear.
If @A itself is NULL, the first branch matches no rows under ordinary equality. The second branch must then retain all qualifying ColB rows. The extra @A IS NULL guard in the next query handles that case. The simpler exclusion copied without this guard is not equivalent for a nullable parameter.
What values can the application submit? Test those values explicitly rather than assuming every parameter is populated. The branches share the same projection and type context. Add other filters consistently to both branches when required. A filter accidentally applied to only one branch changes the result even if the overlap exclusion itself is correct.
DECLARE @A int=1,@B int=2;
SET STATISTICS IO ON;
SELECT ID,ColA,ColB,Payload FROM #ORRows WHERE ColA=@A
UNION ALL
SELECT ID,ColA,ColB,Payload FROM #ORRows
WHERE ColB=@B AND(@A IS NULL OR ColA<>@A OR ColA IS NULL);
SET STATISTICS IO OFF;
Use UNION When Overlap Is Hard to Express
UNION removes duplicate projected rows, while UNION ALL preserves them. UNION can be easier when the overlap condition is complicated, but duplicate elimination adds work and can remove legitimate duplicate-looking rows if the projection omits their stable identity.
The next query includes ID, so two distinct base rows remain distinct even when their displayed payloads match. If you omit ID, the result contract changes: identical projected rows collapse. Decide whether that is intended before choosing UNION as a convenient overlap fix.
Separate branches can also duplicate joins and calculations. Maintenance becomes harder when future changes must be kept synchronized across both sides. Prefer the simplest equivalent statement whose measured access behavior fits the workload. A clean original OR with an adequate index-union plan is a perfectly valid result. The rewrite earns its complexity through evidence, not through a rule that OR is always slow.
DECLARE @A int=1,@B int=2;
SELECT ID,ColA,ColB,Payload FROM #ORRows WHERE ColA=@A
UNION
SELECT ID,ColA,ColB,Payload FROM #ORRows WHERE ColB=@B;Compare Both Directions and Representative Plans
Verify equivalence through a bidirectional comparison of the original and rewritten results. Include the stable key and every required output column. For results where multiplicity matters, also compare grouped row counts because EXCEPT alone uses distinct semantics and can hide duplicate-count differences.
Test first-only, second-only, overlap, NULL columns, NULL parameters, and empty results. Then inspect the actual plans on representative data. Each branch can get a seek, scan, lookup, or another access path according to its estimates. Do not force a seek merely because the rewrite was designed to make one possible.
Compare logical reads, CPU, memory, and returned row count under the same workload conditions. Selectivity varies with parameter values, and a branch strategy that helps one case can cost more in another. Preserve the useful parameter cases in the review instead of reporting only the best-looking execution.
Keep the Contract Visible After Splitting OR Conditions
Document the overlap exclusion beside the statement. It is a correctness rule, not a cosmetic predicate. A future maintainer removing it to simplify the query would reintroduce duplicate rows. Keep nullable-parameter handling visible as well.
Review index write and storage costs independently from the query rewrite. Existing single-column indexes can support the experiment, but adding new covering indexes needs wider workload evidence. A two-branch statement does not automatically justify two more permanent indexes.
Splitting OR conditions is useful when disjoint branches preserve the original result and provide better measured access. Check the existing optimizer plan first, handle overlap and NULL deliberately, and compare representative executions. Keep the form that returns the right rows with a clear, maintainable contract.
Related reading on this blog: Performance: OR vs IN and Performance: Optimizing High Volume OR Conditions through TempTable.
A UNION ALL rewrite is not automatically equivalent, it is two branches whose overlap you must control.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
