Best paging solution using SQL Server 2005?

For a table that size, use a Common-Table Expression (CTE) and ROW_NUMBER; use a small function to calculate the records to bring back based on @PageNumber and @PageSize variables (or whatever you want to call them). Simple example from one of our stored procedures:

-- calculate the record numbers that we needDECLARE @FirstRow INT, @LastRow INTSELECT  @FirstRow   = ((@PageNumber - 1) * @PageSize) + 1,        @LastRow    = ((@PageNumber - 1) * @PageSize) + @PageSize;WITH CTE AS(    SELECT [Fields]           , ROW_NUMBER() OVER (ORDER BY [Field] [ASC|DESC]) as RowNumber     FROM [Tables]    WHERE [Conditions, etc])SELECT *        -- get the total records so the web layer can work out       -- how many pages there are       , (SELECT COUNT(*) FROM CTE) AS TotalRecordsFROM CTEWHERE RowNumber BETWEEN @FirstRow AND @LastRowORDER BY RowNumber ASC

One of the best discussions of various paging techniques I've ever read is here: SQL Server 2005 Paging – The Holy Grail. You'll have to complete a free registration on SQLServerCentral.com to view the article, but it's well worth it.

Even this should help..

SELECT * FROM (     SELECT Row_Number() OVER(order by USER_ID) As RowID,    COUNT (USER_ID) OVER (PARTITION BY null) AS TOTAL_ROWS,     select name from usertbl) As RowResults WHERE RowID Between 0 AND 25

Not sure if its better than @keith version.