Elegant pagination with MS SQL 2005
I’ve never found a great solution for pagination in SQL 2000. I much prefer MySQL’s syntax: SELECT * FROM table LIMIT 100,10. That will get you 10 records starting at the 100th record. Why MS SQL still doesn’t offer an opposite to TOP, I don’t know. Yes, I’ve seen the double TOP method where you sort your data twice to end up with the last X rows. It works, but its not exactly elegant.
But I have come across a new method for pagination using some new 2005 features that is pretty neat. What I really like about this solution is that it gives you a count of the total number of records, which you usually don’t get with other pagination methods.
SELECT TOP #Val(arguments.maxRows)# * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY your sort order here) AS RowNumber, *, TotalRows=Count(*) OVER()
FROM tableName WHERE your criteria here
) _tmpInlineView
WHERE RowNumber >= <cfqueryparam value="#Arguments.startRow#">This works great, is fast, and as a bonus includes the total number of rows!