How to structure predicate to trigger a clustered index seek after a certain record with a composite key?

I have a table with a 4-part composite clustered primary key (A, B, C, D), ordered on A,B,C,D.

I must walk this entire table in batches by taking N records, then taking the next N records starting at whatever value comes after the last key examined (K1,K2,K3,K4). The key values are not contiguous.

I'm trying to construct the predicate to seek to the next batch of records, given a last read record with composite key (K1,K2,K3,K4).

If the ID wasn't composite, I'd just run a select statement like "select top N from Table where ID > K1". However, because it's a 4-part composite key, I have to construct a special predicate to deal with the fact that D can be less than K4, as long as any or all of A, B, and C are greater than K1, K2, and K3.

In some database engines, I understand that such a composite key comparison can be made using set syntax like:

where (A,B,C,D) > (K1,K2,K3,K4)

Question 1: Does SQL Server support the above set comparison syntax?

If not, then I suspect I'll have to structure the predicate like this:

One Key
Two Keys
Three Keys
Four Keys

where (A > K1)
order by A
where (A = K1 and B > K2)
OR (A > K1)
order by A, B
where (A = K1 and B = K2 and C > K3)
OR (A = K1 and B > K2)
OR (A > K1)
order by A, B, C
where (A = K1 and B = K2 and C = K3 and D > K4)
OR (A = K1 and B = K2 and C > K3)
OR (A = K1 and B > K2)
OR (A > K1)
order by A, B, C, D

Question 2: Are the above predicates equivalent to the set comparison (A,B,C,D) > (K1,K2,K3,K4)? Or am I missing conditions.

Question 3: Given that the order by statement matches the clustered index key order, is SQL Server database engine smart enough to recognize this predicate's form and choose the optimal option of seeking to the record with key (K1, K2, K3, K4) and then simply scanning forward from there starting at the next record? Or will it perform a series of seeks for for each part of the predicate combined by an OR operator? I would image that other database engines that recognize the format "where

• Does SQL Server support the above set comparison syntax?

No.

• Are the above predicates equivalent to the set comparison (A,B,C,D) > (K1,K2,K3,K4)?

Yes.

• Given that the order by statement matches the clustered index key order, is SQL Server database engine smart enough to recognize this predicate's form and choose the optimal option of seeking to the record with key (K1, K2, K3, K4) and then simply scanning forward from there starting at the next record? Or will it perform a series of seeks for each part of the predicate combined by an OR operator?

Separate seeking operations within a single Index Seek operator. You may need to write your statements carefully to get this most optimized outcome, depending on your SQL Server version.

See the related Q & A:

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

• Most cost efficient way to page through a poorly ordered table?

StackExchange Database Administrators Q#321946, answer score: 7