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�
Page 1 = 1->10 framesize = 10
Page 2 = 11->20 framesize = 10
Page 3 = 21->21 framesize = 1
Thus when reaching the end of the paging you select (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
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.