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:
SELECT SQL_CALC_FOUND_ROWS
Users.user_id, Users.user_fname, Users.user_lname, Users.user_email, Users.user_isActive
FROM wiki_users as Users
WHERE Users.user_isActive =
LIMIT
SELECT found_rows() AS foundRows
First of all, do you see any problems with this approach?
Second, how would I reproduce this in MSSQL?
Al
You might check out this tech article in MSDN which provides 3 different solutions for paging in MS SQL 2000.
http://msdn.microsoft.com/en-us/library/ms979197.aspx
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...