Simple Data Paging

• Data Paging Procedure - 1 Kb

Introduction

This article will show you how to do really, really simple data paging in Microsoft SQL Server 2000.

Background

I required the need for data paging when I started on a Grid Processing projecting using http://www.alchemi.net/ grid processing framework, each node (executer) will select a small frame of data from a table and process that data into a report. And it was not freezable to do one big select and divide the data among the executers as this will just spur up network traffic and cause a bottle neck at the remoting objects.

What is data paging?

Lets say you have 100 000 rows of data and you only want to view 1000 rows at a time (frame of data), you will need to Page trough the data a 100 times, each time you page you view a new frame.

How the stored procedure works

It selects all the data till the end of the frame size.
Example: If you are on page 5 and you are viewing 100 rows at a time
So 5*100 = Selects rows 1….500

It then swaps the ordering to display the rows reverse order 500…1

And finally it only selects the top 100 and reorders the display.

-- Page = 5
-- Frame Size = 100
SELECT  myColumn
FROM  (SELECT TOP 100 myColumn
    FROM  (SELECT TOP 500 myColumn -- Page x Frame
        FROM myTable
        ORDER BY myIDColumn) SUBSEL
    ORDER BY myIDColumn DESC) PAGESEL -- swap order
ORDER BY myIDColumn -- reorder

Actual code

The above example is using a myIDColumn this column should be a unique value for each row, IE: identity column would be perfect to do this. If the values are not unique for each row the sorting may or can I say WILL not work correctly.

NOTE: Due to the nature of an ORDER BY in SQL your sort column, needs to be included in your select.

CREATE PROC DataPaging
 @iPage INT,
 @iFrame INT,
 @sColumns VARCHAR(4000),
 @sTable VARCHAR(4000),
 @sSortColumn VARCHAR(4000)
AS
 DECLARE @sSQL VARCHAR(4000)
 DECLARE @iCount INT 

 -- ========================================================
 -- CHECK TABLE COUNT
 -- ======================================================== 
 CREATE TABLE #Count (tablecount int) 

 -- get count insert into temp table #count
 SET @sSQL = 'INSERT INTO #Count SELECT COUNT(*) tablecount FROM ' + @sTable 
 EXEC(@sSQL)
 
 -- set variable size
 SELECT @iCount = tablecount FROM #Count 

  -- drop temp
 DROP TABLE #count   -- Check if frame count more than selection
 IF ((@iFrame * @iPage) > @iCount)
  SET @iCount = @iCount - (@iFrame * (@iPage - 1))
 ELSE
  SET @iCount = 0 
  
 -- ========================================================
 -- CREATE SQL
 -- ========================================================  IF (@iCount = 0)
  BEGIN
   SET @sSQL = 'SELECT ' + @sColumns + ' ' +
      'FROM (SELECT TOP ' + cast(@iFrame as varchar(100)) + ' ' + @sColumns +
      '   FROM (SELECT TOP ' + cast(@iPage * @iFrame as varchar(100)) + ' ' + @sColumns +
      '     FROM ' + @sTable +
      '     ORDER BY ' + @sSortColumn + ') SUBSEL' +
      '   ORDER BY ' + @sSortColumn + ' DESC) PAGESEL ' +
      'ORDER BY ' + @sSortColumn
  END
 ELSE
  BEGIN
   SET @sSQL = 'SELECT ' + @sColumns + ' ' +
      'FROM (SELECT TOP ' + cast(@iCount as varchar(100)) + ' ' + @sColumns +
      '   FROM (SELECT TOP ' + cast(@iPage * @iFrame as varchar(100)) + ' ' + @sColumns +
      '     FROM ' + @sTable +
      '     ORDER BY ' + @sSortColumn + ') SUBSEL' +
      '   ORDER BY ' + @sSortColumn + ' DESC) PAGESEL ' +
      'ORDER BY ' + @sSortColumn
  END
 
 -- ========================================================
 -- EXECUTE
 -- ========================================================
 EXEC (@sSQL) GO

When querying I check the table count relevant to the frame passed and count, the reason I do this is as follows

Lets say you have 21 records you paging your frame size is 10, thus you will need to page 3 times…

<st1:personname><st1:personname>_{Page 1 = 1->10    framesize = 10
<st1:personname><st1:personname>Page 2 = 11->20  framesize = 10
<st1:personname><st1:personname>Page 3 = 21->21  framesize = 1}

Thus when reaching the end of the paging you select (<st1:personname><st1:personname>Page * Frame Size) when doing the swap.  You will need to select only the remaining records when doing the swap in our case that will be 1, instead of the usual 10.

Execution<o:p>

 EXEC GenericPaging 
  1,
  500,
  'myColumn1,myColumn2,myColumn3', 
  'myTable',
  'myIDColumn'

History

This is my first attempt at data paging it works for my application and done any extensive testing with it. If you can improve on it your more than welcome to send me your findings.