patternsqlMinor

Double range query on one column uses wrong seek predicate

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

seekcolumnrangequeryusespredicateonedoublewrong

Problem

This problem comes up in the real world, but is already present in the following trivial example. I am using SQL Server 2017.

Given a table with only one non-id column, which takes only one value:

create table #test (id bigint not null, status smallint not null)

/* Populate with 10000 times value 10 */
;WITH numbers(Number) AS
(SELECT 1 AS Number
 UNION ALL
 SELECT Number+1 FROM numbers where Number<10000
)

insert into #test (id,status)
select number,10 from numbers option(maxrecursion 10000)

/* Create fresh index */
create index index_status on #test ([status])
DBCC SHOW_STATISTICS ("tempdb..#test", 'index_status') WITH HISTOGRAM

Now we query a double range on this column (0 results, since the only value is outside both ranges):

select 1
from #test
where status  2

The chosen execution plan uses an Index Seek, with

Seek Predicate "status 2"

Predicate "status

and consequently needs to read all 10000 rows. This surprises me, since I can read off the index statistics to conclude that a Seek Predicate on "status < 10" would be more efficient, since this already filters out all rows.

Question: Why are Seek Predicate and Predicate chosen 'the wrong way'?

I am not interested in alternatives or workarounds, I am only interested in understanding this choice of execution plan. For example we can rewrite the query using a UNION ALL on both range queries seperately, forcing two efficient Seek Predicates:

select 1
from #test
where status  2 and status < 10

Solution

Running

dbcc traceon (3604,8606)

And then the query with a #temp table the final tree before cost based optimisation looks as follows (with the NE/ <> predicate first and the LT/ 2 and status 2 and residual on < 10

My supposition is that the index matching code just picks the first seekable predicate available in input tree order in the rare case that there are multiple competing seek predicates for the same column.

Specifically the SelPredNorm transformation rule appears to cause the predicate re-ordering. The following gives the desired plan.

select 1
from #test
where status  2
option (queryruleoff SelPredNorm);

As for why this does not kick in (in this case) with auto parameterised queries that question is still not addressed.

Code Snippets

dbcc traceon (3604,8606)

*** Tree After Project Normalization ***
        LogOp_Project

            LogOp_Select

                LogOp_Get TBL: #test #test TableID=-1583795211 TableReferenceID=0 IsRow: COL: IsBaseRow1001 

                ScaOp_Logical x_lopAnd

                    ScaOp_Comp x_cmpNe

                        ScaOp_Identifier QCOL: [#test].status

                        ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=2)

                    ScaOp_Comp x_cmpLt

                        ScaOp_Identifier QCOL: [#test].status

                        ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=10)

            AncOp_PrjList 

                AncOp_PrjEl COL: Expr1003 

                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)

*** Tree After Project Normalization ***
        LogOp_Project

            LogOp_Select

                LogOp_Get TBL: test test TableID=645577338 TableReferenceID=0 IsRow: COL: IsBaseRow1001 

                ScaOp_Logical x_lopAnd

                    ScaOp_Comp x_cmpLt

                        ScaOp_Identifier QCOL: [tempdb].[dbo].[test].status

                        ScaOp_Identifier COL: ConstExpr1004 

                    ScaOp_Comp x_cmpNe

                        ScaOp_Identifier QCOL: [tempdb].[dbo].[test].status

                        ScaOp_Identifier COL: ConstExpr1005 

            AncOp_PrjList 

                AncOp_PrjEl COL: Expr1003 

                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)

select 1
from #test
where status < 10
and status <> 2
option (queryruleoff SelPredNorm);

Context

StackExchange Database Administrators Q#282608, answer score: 4

Revisions (0)

No revisions yet.