Lexicographical comparison in SQL SERVER

Question

I have to execute queries to select rows where the composite key is lexicographically less than or greater than some given tuple of values:

SELECT TOP 100
       *
FROM PALISBSD
WHERE (recbsd_key_bsd_key_bsd1_bsd_jar < '03'
    OR (recbsd_key_bsd_key_bsd1_bsd_jar = '03'
    AND (recbsd_key_bsd_key_bsd1_bsd_pro < 'L'
       OR (recbsd_key_bsd_key_bsd1_bsd_pro = 'L'
       AND (recbsd_key_bsd_key_bsd1_bsd_az1_bsd_kst < 'C'
          OR (recbsd_key_bsd_key_bsd1_bsd_az1_bsd_kst = 'C'
          AND (recbsd_key_bsd_key_bsd1_bsd_az1_bsd_azl < '00017004'
             OR (recbsd_key_bsd_key_bsd1_bsd_az1_bsd_azl = '00017004'
             AND (recbsd_key_bsd_key_bsd1_bsd_linr < '0001'
                OR (recbsd_key_bsd_key_bsd1_bsd_linr = '0001'
                AND (recbsd_key_bsd_key_bsd1r_bsd_sa < 'D'
                   OR (recbsd_key_bsd_key_bsd1r_bsd_sa = 'D'
                   AND (recbsd_key_bsd_key_bsd1r_bsd_an < 'PTZ')))))))))))))
ORDER BY recbsd_key_bsd_key_bsd1_bsd_jar DESC,
         recbsd_key_bsd_key_bsd1_bsd_pro DESC,
         recbsd_key_bsd_key_bsd1_bsd_az1_bsd_kst DESC,
         recbsd_key_bsd_key_bsd1_bsd_az1_bsd_azl DESC,
         recbsd_key_bsd_key_bsd1_bsd_linr DESC,
         recbsd_key_bsd_key_bsd1r_bsd_sa DESC,
         recbsd_key_bsd_key_bsd1r_bsd_an DESC;

A clustered index exists that contains all the columns named here, in exactly this order. However its often not used to perform a seek. In fact I can accelerate a lot, but not all of my queries by providing the WITH (FORCESEEK) hint or by prefixing the where term with something like recbsd_key_bsd_key_bsd1_bsd_jar <= '03' AND.

My question is: Is there a better way to express lexicographical comparison, so MS SQL Server will better utilize the indices for the comparison? In Postgres this would be expressed via tuple comparison (col1, col2) < ('FOO', 'BAR'), but no such feature exists in MS SQL Server.

Edit: I cannot create a concatenated computed column, because the key might contain non-character fields as well.

@Larnu No, actually it looks like some of those are doubled up. — user1588931, Apr 13 '22 at 14:00
No this is not possible, and the best method is the one you already have. Reducing the number of columns involved might help. By the way, the term you are looking for is Keyset Pagination, see https://stackoverflow.com/a/70520457/14868997 and https://use-the-index-luke.com/no-offset — Charlieface, Apr 13 '22 at 14:42
@Larnu Half of them are necessary, because of the precedence of `OR` — Charlieface, Apr 13 '22 at 14:44

score 0 · Accepted Answer · answered Apr 14 '22 at 06:01

Using Statements instead of PreparedStatements and rearranging the filter to look like the following, as opposed to the form in my question, actually seems to make SQL Server use the index:

SELECT TOP 100 * FROM PALISBSD
WHERE
    (recbsd_key_bsd_key_bsd1_bsd_jar < '03') OR
    (recbsd_key_bsd_key_bsd1_bsd_jar = '03' AND recbsd_key_bsd_key_bsd1_bsd_pro < 'L') OR
    (recbsd_key_bsd_key_bsd1_bsd_jar = '03' AND recbsd_key_bsd_key_bsd1_bsd_pro = 'L' AND recbsd_key_bsd_key_bsd1_bsd_az1_bsd_kst < 'C') OR
    (recbsd_key_bsd_key_bsd1_bsd_jar = '03' AND recbsd_key_bsd_key_bsd1_bsd_pro = 'L' AND recbsd_key_bsd_key_bsd1_bsd_az1_bsd_kst = 'C' AND recbsd_key_bsd_key_bsd1_bsd_az1_bsd_azl < '00017004') OR
    (recbsd_key_bsd_key_bsd1_bsd_jar = '03' AND recbsd_key_bsd_key_bsd1_bsd_pro = 'L' AND recbsd_key_bsd_key_bsd1_bsd_az1_bsd_kst = 'C' AND recbsd_key_bsd_key_bsd1_bsd_az1_bsd_azl = '00017004' AND recbsd_key_bsd_key_bsd1_bsd_linr < '0001') OR
    (recbsd_key_bsd_key_bsd1_bsd_jar = '03' AND recbsd_key_bsd_key_bsd1_bsd_pro = 'L' AND recbsd_key_bsd_key_bsd1_bsd_az1_bsd_kst = 'C' AND recbsd_key_bsd_key_bsd1_bsd_az1_bsd_azl = '00017004' AND recbsd_key_bsd_key_bsd1_bsd_linr = '0001' AND recbsd_key_bsd_key_bsd1r_bsd_sa < 'D') OR
    (recbsd_key_bsd_key_bsd1_bsd_jar = '03' AND recbsd_key_bsd_key_bsd1_bsd_pro = 'L' AND recbsd_key_bsd_key_bsd1_bsd_az1_bsd_kst = 'C' AND recbsd_key_bsd_key_bsd1_bsd_az1_bsd_azl = '00017004' AND recbsd_key_bsd_key_bsd1_bsd_linr = '0001' AND recbsd_key_bsd_key_bsd1r_bsd_sa = 'D' AND recbsd_key_bsd_key_bsd1r_bsd_an < 'PTZ')
ORDER BY
    recbsd_key_bsd_key_bsd1_bsd_jar DESC,
    recbsd_key_bsd_key_bsd1_bsd_pro DESC,
    recbsd_key_bsd_key_bsd1_bsd_az1_bsd_kst DESC,
    recbsd_key_bsd_key_bsd1_bsd_az1_bsd_azl DESC,
    recbsd_key_bsd_key_bsd1_bsd_linr DESC,
    recbsd_key_bsd_key_bsd1r_bsd_sa DESC,
    recbsd_key_bsd_key_bsd1r_bsd_an DESC;

I am aware of SQL Injections and I'm preventing them in other ways.

Lexicographical comparison in SQL SERVER

1 Answers1