InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 15:45:24 GMT20http://www.sqlservercentral.com/Forums/Topic707076-1509-1.aspxHere'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.[hr]DECLARE @numberofitemsperpage INTDECLARE @numberofpages INTDECLARE @currentpage int--change the following two variables to your requirementsSET @numberofitemsperpage = 10SET @numberofpages = 5SET @currentpage =0WHILE @currentpage < @numberofpages BEGIN SELECT a.* FROM (SELECT row_number() OVER (ORDER BY [b]whatevercolumnyouwant[/b]) AS ROW, * FROM [b]yourtablenamehere[/b]) aWHERE ROW >= @currentpage * @numberofitemsperpage +1 AND Row <= (@currentpage+1) * @numberofitemsperpage IF @@ROWCOUNT = 0 BREAK SET @currentpage = @currentpage +1ENDTue, 26 May 2009 04:24:16 GMTcengland0http://www.sqlservercentral.com/Forums/Topic707076-1509-1.aspx... or by parameterizing the top statement maybe...[code]declare @i intset @i = 1000select top(@i) * from tablename order by ID[/code]Tue, 26 May 2009 01:14:01 GMTb4ndthttp://www.sqlservercentral.com/Forums/Topic707076-1509-1.aspxIf I'm not mistaken, since this uses the identity column, it might not always produce the same number of columns. For example, if you deleted a couple rows in the middle, it would still think those rows were there using your between statement.Why not use top? For example, if you want chucks of 1000 records:select top 1000 * from a_table order by IDYou can then capture the last identity that was selected and your next statement would be:select top 1000 * from a_table where ID > @lastident order by IDFri, 22 May 2009 08:39:34 GMTcengland0http://www.sqlservercentral.com/Forums/Topic707076-1509-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/T-SQL/66788/">Split by a row (count) number of a query results </A>[/B]Wed, 29 Apr 2009 11:20:04 GMTdiaz.bernabe