Great article. I have been trying to implement your ideas but have come unstuck with what I am doing. The issue revolves around implemenmting the key seek method when trying to get distinct results from the first query. ie

with Keys as (

SELECT distinct

....

)

when using distinct you can't use the row_number over mothod as you don't get sequential results, though this can be resolved by adding another section below the keys part and moving the row_number to that.

distinctKeys AS

(

SELECT TOP (@page * @recsPerPage)

rn = ROW_NUMBER() OVER (ORDER by '+@SQLOrderByTemp+')

FROM keys sk

)

With that working the problem is taht I can't figure out how to get the count of keys quickly. Because I am using the distinct in a way that means that I can't just get the pk back but have to use a number of columns you can't guarantee that the count will match the select.

The only way to do this seems to ve to move the count sub query to the distinctKeys section and remove the TOP part of the Keys section. This works but obviously I am now having to get all the records back from keys which is not very efficient on large sets.

I hope that makes sense! Any help on this would be much appreciated.

cheers

Tim