SQL server 2000 paging on a table with three keys

Question

I've been trying to solve this problem for a few days now without much luck. I have found loads of resources that talk about paging on SQL Server 2000 both here and on codeproject.

The problem I am facing is trying to implement some sort of paging mechanism on a table which has three keys which make up the primary key. Operator, CustomerIdentifier, DateDisconnected.

Any help/pointers would be greately appreciated

Answer 1

SQL Server 2000 doesn't have the handy row_number function, so you'll have to auto-generate a row number column with a subquery, like so:

select
    *
from
    (select
        *,
        (select count(*) from tblA where 
            operator < a.operator
            or (operator = a.operator
                and customeridentifier < a.customeridentifier)
            or (operator = a.operator
                and customeridentifier = a.customeridentifier
                and datedisconnected <= a.datedisconnected)) as rownum
     from
        tblA a) s
where
    s.rownum between 5 and 10
order by s.rownum

However, you can sort those rows by any column in the table -- it doesn't have to use the composite key. It would probably run faster, too!

Additionally, composite keys are usually a flag. Is there any particular reason you aren't just using a surrogate key with a unique constraint on these three columns?