Index seek to specific multi-column key, then get some rows in lexicographical order

Consider the following sample table with a multi-column index:
create table BigNumbers (
col1 tinyint not null,
col2 tinyint not null,
col3 tinyint not null,

index IX_BigNumbers clustered (col1, col2, col3)
)

DECLARE @n INT = 100;

DECLARE @x1 INT = 0;
DECLARE @x2 INT = 0;
DECLARE @x3 INT = 0;

SET NOCOUNT ON;

WHILE @x3

My goal is now to get a couple of rows from that index, starting with a given key.

What sounds trivial is somewhat complicated as there is no easy way in SQL to express the lexicographical order the index is in:

DECLARE @x1 INT = 60;
DECLARE @x2 INT = 40;
DECLARE @x3 INT = 98;

select top 5 *
from BigNumbers 
where
  col1 > @x1 or
 (col1 = @x1 and
   (col2 > @x2 or
   (col2 = @x2 and col3 >= @x3)))
order by col1, col2, col3

The correct result is:

60  40  98
60  40  99
60  40  100
60  41  0
60  41  1

However, the query plan tells me this uses an index scan.

The underlying index should be able to seek and return the first few rows greater or equal to (@x1, @x2, @3) in the index's order, but since there's no way in SQL to express this intent easily, the query planner appears unable to take the hint and instead does a scan.

Index hints don't help and a FORCESEEK` gives a horrendous plan.

Interestingly, the following two-column version works:

select top 5 *
from BigNumbers 
where
  col1 = @x1 and
   (col2 > @x2 or
   (col2 = @x2 and col3 >= @x3))
order by col1, col2, col3

I'm not sure why that is, but not only does the plan use a seek, it also correctly reports having touched only 5 rows:

I'd like to know whether someone knows of a way to query a couple of rows of an index greater or equal than a given value tuple with a simple seek reliably.

It seems odd that the database should obscure this fundamental capability under its higher level abstractions.

If anyone is interested to know what problem this is for, I'm developing a generic UI for SQL databases. The most obvious place where you need th

What you are referring to is a row-comparison, and you are using it for a Keyset Pagination query. In DBMSs that support it, you can simply do
where (col1, col2, col3) >= (@x1, @x2, @x3)


SQL Server does not support this however. What it does support is an Index Seek on multiple ranges. So the single Index Seek becomes two or three, but the ordering is understood and maintained by the compiler so it effectively acts as a single seek over one range.

It uses this on a number of different types of queries, primarily IN lists and OR queries. It also uses it on row comparison logic, which is very useful when doing Keyset Pagination.

In your case, it is not recognizing this pattern. It seems this is because you are using nested boolean logic to express it. It will successfully recognize the following logic, which is exactly the same semantically
where (col1 = @x1 and col2 = @x2 and col3 >= @x3)
or (col1 = @x1 and col2 > @x2)
or (col1 > @x1)


db<>fiddle

Exactly why it recognizes one and not the other is unclear. Perhaps someone with access to a debugger and/or knowledge of the optimizer rules can elaborate.

StackExchange Database Administrators Q#316232, answer score: 9