sqlpaginationsql-server-2000

SQL server 2000 paging on a table with three keys


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


Solution

  • 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?