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

Question

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 <= @n BEGIN
    SET @x2 = 0;
    WHILE @x2 <= @n BEGIN
        SET @x1 = 0;
        WHILE @x1 <= @n BEGIN
            insert into BigNumbers values (@x1, @x2, @x3);
            SET @x1 = @x1 + 1;
        END;
        SET @x2 = @x2 + 1;
    END;
SET @x3 = @x3 + 1;
END;

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 this is for a "load more" button where you want to continue showing index content for a given starting point. If this is not possible in general, the workaround would be to first query fixing all but the last column, and do a second query, and so on. It would be a bit of a shame having to do that though.

Answer 1

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.

Answer 2

In a perfect world, people would be able to write queries in any logically-equivalent form and the optimizer would produce the same optimal execution plan in all cases.

This isn't a practical proposition—the optimizer has a limited amount of time to work with, and an incomplete knowledge of the possible transformations. We are therefore stuck with sometimes needing to express our requirement in a particular written form to get the best outcome.

SQL Server does make some efforts to standardize (normalize) the logical form of the submitted statement, but they're not exhaustive. For example, logic clauses are transformed to negation normal form (NNF), but not conjunctive (CNF) or disjunctive (DNF) normal form. Transformation to NNF is always easy and compact; that's not always so for CNF or DNF.

This is a shame in your case because the DNF form happens to be easy to derive and works well with the index-matching logic, as shown in another answer:

DECLARE 
    @x1 tinyint = 60,
    @x2 tinyint = 40,
    @x3 tinyint = 98;
SELECT TOP (5) 
    BN.col1, 
    BN.col2, 
    BN.col3 
FROM dbo.BigNumbers AS BN
WHERE 
    (BN.col1 = @x1 AND BN.col2 = @x2 AND BN.col3 >= @x3)
    OR (BN.col1 = @x1 AND BN.col2 > @x2)
    OR (BN.col1 > @x1)
ORDER BY 
    BN.col1, 
    BN.col2, 
    BN.col3;

This produces a TRIVIAL execution plan with three separate seeking operations inside the clustered index seek, executed sequentially (with short-circuiting):

The ideal, as also already noted, would be for SQL Server to implement row constructors, with the logic expanded as needed into the optimal form for index matching. This has been long requested, but frustratingly not delivered yet. For the time being, we need to write anchor-based pagination queries in a particular way.

A very slightly less efficient, but still decent solution is:

DECLARE 
    @x1 tinyint = 60,
    @x2 tinyint = 40,
    @x3 tinyint = 98;
SELECT BN.col1, BN.col2, BN.col3 
FROM dbo.BigNumbers AS BN 
WHERE BN.col1 = @x1 
AND BN.col2 = @x2 
AND BN.col3 >= @x3
UNION ALL
SELECT BN.col1, BN.col2, BN.col3 
FROM dbo.BigNumbers AS BN 
WHERE BN.col1 = @x1 
AND BN.col2 > @x2
UNION ALL
SELECT BN.col1, BN.col2, BN.col3 
FROM dbo.BigNumbers AS BN 
WHERE BN.col1 > @x1
ORDER BY BN.col1, BN.col2, BN.col3
OFFSET 0 ROWS 
FETCH FIRST 5 ROWS ONLY;

This plan has three single seeks, but each must produce at least one row for the merge concatenation comparison, so none can be completely short-circuited.

db<>fiddle