Paging in MSSQL 2005-Greater? Help

Paging in MSSQL 2005-Greater? Help

Posted by Luis Majano
Feb 19, 2009 00:00:00 UTC
I wanted to shoot out on this as I do not use MSSQL 2005 and greater too much and need help on what would be the best and great way to do record paging. I am so used to this in MySQL and you can see a sample of MySQL below:


Users.user_id, Users.user_fname, Users.user_lname, Users.user_email, Users.user_isActive

FROM wiki_users as Users

WHERE Users.user_isActive =


SELECT found_rows() AS foundRows

First of all, do you see any problems with this approach?

Second, how would I reproduce this in MSSQL?


You might check out this tech article in MSDN which provides 3 different solutions for paging in MS SQL 2000.

Daniel Short

I generally do this with a common table expression:

WITH tempTable AS ( SELECT P.FirstName , P.LastName , ROW_NUMBER() OVER(ORDER BY P.LastName, P.FirstName) AS RowNumber FROM People P (NOLOCK) WHERE P.Active = 1 )

SELECT *, (SELECT Max(RowNumber) FROM tempTable) AS Records FROM tempTable WHERE RowNumber >= 11 AND RowNumber 20 ORDER BY LastName, FirstName

CTEs are your friend...