Comparing Tuples in SQL

Question

Is there any more convenient way to compare a tuple of data in T-SQL than doing something like this:

SELECT TOP 100 A, B
FROM MyTable
WHERE (A > @A OR (A = @A AND B > @B))
ORDER BY A, B

Basically I'm looking for rows with (A, B) > (@A, @B) (the same ordering as I have in the order by clause). I have cases where I have 3 fields, but it just gets even uglier then.

Answer 1

No. That's as simple as it gets. In fact, you can prove this is the simplest by drawing out a truth table. (Set 3 columns: A<@A, A=@A, and A>@A. Set 3 rows: B<@B, B=@B, B>@B.)

       A<@A | A=@A | A>@A
--------------------------
B<@B |  F   |  F   |  T
--------------------------
B=@B |  F   |  F   |  T
--------------------------
B>@B |  F   |  T   |  T

Answer 2

No, but you can combine the members of the tuple into something that is directly comparable. For example if A and B are both numbers between 0..99, add A*100+B and @A*100+@B and compare those. If they're both strings, pad with spaces to the maximum length and concatenate.

This is a nasty trick you can sometimes use to get yourself out of a hole, but otherwise it's best not used. For one thing it will defeat indexing, so you'll be doing a full table scan each query.