Clustered Index 'Seek predicate' and 'predicate' on the same column

I have a clustered index primary key column and I'm doing a range query on it.
The problem is the scan only uses the first range part for the seek predicate, leaving the other side of the range as a residual predicate.
which causes to read all the rows up to the @upper limit

I'm using two parameters for the range:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from messages
where msg_id between @lower+1 and @upper;

In that case the actual execution plan shows:

• predicate: messages.msg_id >= lower

• seek predicate: messages.msg_id

• rows read: 1005

Table definition (Simplified):

CREATE TABLE [dbo].[messages](
    [msg_id] [numeric](18, 0) IDENTITY(0,1) NOT NULL,
    [col2] [varchar](32) NOT NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED 
(
    [msg_id] ASC
)

More information

When used with constants instead of variables both of the predicates are 'Seek'
Have tried Option (Optimize for (@lower=1000)), without success

Starting with your original query:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower+1 and @upper;

The 1 that you added has a data type of integer by default. When adding an integer value to a numeric(18,0) value SQL Server applies the rules of data type precedence. int has a lower precedence so it gets converted to a numeric(1,0). Your query is equivalent to the following:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower+CAST(1 AS NUMERIC(1, 0)) and @upper;

A different set of rules around Precision, scale, and Length is applied to determine the data type of the expression involving @lower. It isn't safe to just use NUMERIC(18,0) because that could be overflowed (consider 999,999,999,999,999,999 and 1 as an example). The rule that applies here is:

╔═══════════╦═════════════════════════════════════╦════════════════╗
║ Operation ║          Result precision           ║ Result scale * ║
╠═══════════╬═════════════════════════════════════╬════════════════╣
║ e1 + e2   ║ max(s1, s2) + max(p1-s1, p2-s2) + 1 ║ max(s1, s2)    ║
╚═══════════╩═════════════════════════════════════╩════════════════╝

For your expression, the resulting precision is:

max(0, 0) + max(18 - 0, 1 - 0) + 1 = 0 + 18 + 1 = 19

and the resulting scale is 0. You can verify this by running the following code in SQL Server:

declare
@lower numeric(18,0) = 1000,
@upper numeric(18,0) = 1005;

SELECT 
  SQL_VARIANT_PROPERTY(@lower+1, 'BaseType') lower_exp_BaseType
, SQL_VARIANT_PROPERTY(@lower+1, 'Precision') lower_exp_Precision
, SQL_VARIANT_PROPERTY(@lower+1, 'Scale') lower_exp_Scale;

This means that your original query is equivalent to the following:

declare
    @lower numeric(19,0) = 1000 + 1,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower and @upper;

SQL Server can only use @lower to do a clustered index seek if the value can be implicitly converted to NUMERIC(18, 0). It is not safe to convert a NUMERIC(19,0) value to NUMERIC(18,0). As a result the value is applied as a predicate instead of as a seek predicate. One workaround is to do the following:

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between TRY_CAST(@lower+1 AS NUMERIC(18,0)) and @upper;

That query can process both filters as seek predicates:

My advice is to change the data type in the table to BIGINT if possible. BIGINT requires one fewer byte than NUMERIC(18,0) and benefits from performance optimizations not available to NUMERIC(18,0) including better support for bitmap filters.

StackExchange Database Administrators Q#214757, answer score: 9