Here's the ultimate method.

I tried to create an example using the identity column but it required additional queries which wasn't so efficient. Still, it was more efficient than creating temp tables.

Here's a method that works with SQL 2005 using the row_number() function. I tested it and it works flawlessly. Feedback would be appreciated.

DECLARE @numberofitemsperpage INT

DECLARE @numberofpages INT

DECLARE @currentpage int

--change the following two variables to your requirements

SET @numberofitemsperpage = 10

SET @numberofpages = 5

SET @currentpage =0

WHILE @currentpage = @currentpage * @numberofitemsperpage +1 AND Row <= (@currentpage+1) * @numberofitemsperpage

IF @@ROWCOUNT = 0 BREAK

SET @currentpage = @currentpage +1

END