First I am back a day later then I expected and definatly had hoped for, sorry for that. And please someone FIX this forum (smileys in code, persistent extended page widths even after I reformatted the code to fit).

Now my 'paging' code that I said would contribute. Bear in mind I work in an enviromment that is able to send parameterized queries from the application layer to the database. The SQL code itself originates from the application layer most of the time where queries are created dynamically with relativly ease.

What I created a while back was a method of reducing the SQL complexity when dealing with paging on the application side. For it to work the main query needs to be written in a CTE and return a sequence number that can be sorded on in the actual query. It is also used by the paging logic in this query to exclude unwanted records among other things.

At the end of the normal query (now wrapped in a CTE), an application function is called to generate the paging part of the query for the developer automatically. The part deals with limiting the amount of data is transmidded by SQL sever to the appliation sever. The code also takes care of out of bounds cases and adjusts the page number accordingly. On top of this an optional part generates a comma seperated list of primary key IDs of all the records in the result, including the ones not returned due to paging.

First we need some demo data (10 000 records should do):

-- Create demo table with primary key

--

create table dbo.DemoTable

(

Id int not null

, Name varchar(36) not null

, SomeData varbinary(20) not null

, constraint pk_DemoTable primary key clustered( ID )

)

;

go

-- Insert 10.000 test records into the demo table

--

insert into dbo.DemoTable( Id, Name, SomeData )

select

numbers.N

, newID()

, hashBytes( 'SHA1', cast( numbers.N as varchar ) )

from

(

select top 10000

row_number() over ( order by t1.object_id )

from

master.sys.All_Columns as t1 with( nolock )

cross join master.sys.All_Columns as t2 with( nolock )

) as numbers( N )

go

-- Index the demo table

--

create index ix_DemoTable_Name on dbo.DemoTable ( Name );

create index ix_DemoTable_SomeData on dbo.DemoTable ( SomeData );

go

-- Show the first few records to get a feel of what the data looks like

--

select top 10 * from dbo.DemoTable where Id <= 10

go

Next is a demo query itself that demonstrates the techniques used and makes clear how easy the results can be processed further:

-- For demo purposes only: these parameters are normaly injected into

-- the paging secopn of the query as constants

--

declare @pgNum int;

declare @pgSize int;

select @pgNum = 300, @pgSize = 25;

;

with

/* This code is put into the page that shows the grid */

pgFromQ as

(

select

row_number() over

(

-- Order by, to be generated by application code so grid sorting can change over time

order by

demo.Name desc

, demo.Id asc -- ALWAYS include the primary key as the last field when sorting

) as 'pgRowNumber'

, demo.Id

, demo.Name

, demo.SomeData

from

dbo.DemoTable demo

)

Then the developer calls a function that generates the rest of the query.

It requires a few parameters:

1: Page to show (4)

2: Page size (25)

3: Optional field name ("Id" in this example).

When specified it causes a comma seperated list to be generated of all the IDs.

This can be used to 'browse' over the results in the any detail screen.

It looks like (in whatever language you use):

pagerSQL( 4, 25, "Id" )

This code then generates the remainder of the query (2 parts). The first part is some more CTEs that handle page clipping and the like.

, pgCountQ( recordCount, pageCount ) as

(

-- determines the number of pages

select top 1

count(*)

, floor( (count(*) + @pgSize - 1) / @pgSize )

from

pgFromQ

)

, pgPageQ( activePage, recordCount, pageCount ) as

(

select top 1

case

when @pgNum < 1

then 1

when @pgNum > pageCount

then pageCount

else @pgNum

end

, recordCount

, pageCount

from

pgCountQ

)

, pgRangeQ( firstRecord, activePage, recordCount, pageCount ) as

(

select top 1

case

when activePage = 0

then 0

else ( @pgSize * ( activePage - 1 ) + 1 )

end

, activePage

, recordCount

, pageCount

from

pgPageQ

)

-- this is the optional part that is generates the comma separated list of a key field ("Id" in this example)

, pgRecListQ( recIDs ) as

(

select

stuff

(

(

select

cast( ',' as varchar(max) ) + cast( i.Id as varchar )

from

pgFromQ as i

order by

i.pgRowNumber

for

xml path('')

)

, 1

, 1

, ''

)

)

-- end of optional part

The second part of the generated code performs the actual query and makes sure the least amount of data is returned by SQL.

/*

-- And finaly the select that returns the result set (only the first record of the resulting page

-- contains extra such as number of first record, the active page number, the recordcount and the pagecount.

-- And inf choosen also the optional comman separated list of IDs that represent the non-paged result.

*/

select

case when pgRangeQ.recordCount = 0 or pgFromQ.pgRowNumber = pgRangeQ.firstRecord then pgRangeQ.firstRecord else null end as 'pgFirstRecord'

, case when pgRangeQ.recordCount = 0 or pgFromQ.pgRowNumber = pgRangeQ.firstRecord then pgRangeQ.activePage else null end as 'pgActivePage'

, case when pgRangeQ.recordCount = 0 or pgFromQ.pgRowNumber = pgRangeQ.firstRecord then pgRangeQ.recordCount else null end as 'pgRecordCount'

, case when pgRangeQ.recordCount = 0 or pgFromQ.pgRowNumber = pgRangeQ.firstRecord then pgRangeQ.pageCount else null end as 'pgPageCount'

-- this is the optional part that is returns the comma separated list of a key field ("Id" in this example)

, case when pgFromQ.pgRowNumber = pgRangeQ.firstRecord then ( select top 1 recIDs from pgRecListQ ) else null end as 'pgRecordIDs'

-- end of optional part

, pgFromQ.*

from

pgRangeQ

left join pgFromQ on pgFromQ.pgRowNumber between pgRangeQ.firstRecord and pgRangeQ.firstRecord + @pgSize - 1

order by

pgFromQ.pgRowNumber

;

The automaticaly generated code might seem complicated at first but once understood it is not that bad, nor is it expensive. It is nicely hidden from the functionality of the original query and it makes processing the results easy and efficient. The only times I experienced bad performance was when testing with generation of the optional comma separated list over say 100 000 records and when the original/main query was slow. It is always a matter of using the right design for the job, so that ain't a negative for the method itself. It certainly beats all the paging I ever seen before in applications on speed, transmission efficiency and usability.