Avevo già postato sulla paginazione lato server (da db) per paginare i dati su una web form.

Nei giorni scorsi ho avuto modo di rileggere un paio di articoli interessanti e ho cercato di fare il punto e confrontare diverse soluzioni.
In pratica ho confrontato le seguenti 3 tecniche:

• utilizzo di una Common Table Expression (CTE) + funzione RowNumber()
  
• utilizzo di una tabella di appoggio temporanea (#tmp, quindi tempdb)
• utilizzo di una variabile di tipo table (@tmp, quindi memoria)

Con Sql 2k si possono usare solo le ultime 2.

Di seguito lo pseudo-code (di pseudo c'è solo il nome dei campi e delle tabelle). Ho assunto che la tabella abbia una primary key key identity(1,1) e che i field da estrarre siano field_1... field_n. Spero di essere stato chiaro ;)
Provatelo voi stessi su una tabella con qualche milione di righe e vedrete: la soluzione MSSql 2k5 + CTE + ROW_NUMBER() è la migliore. Buon test.

Link utili:

• Paging su SQL Server 2005
• SQL SERVER - 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) - CTE vs. Derived Table
• SQL SERVER - T-SQL Paging Query Technique Comparison - SQL 2000 vs SQL 2005

L'esempio:

DECLARE @PageNumber int
DECLARE @PageSize int
SET @PageNumber = 1;
SET @PageSize = 100;

declare @t1 datetime
declare @t2 datetime
set @t1=getdate();
set @t2=getdate();

 WITH Paged AS
(
SELECT key, field_1,field_2, ... field_n, ROW_NUMBER() OVER (ORDER BY clausola_order) AS RowNumber
FROM table
WHERE (le_nostre_clausole_di_selezione)
)
SELECT key, field_1,field_2, ... field_n, BET
FROM Paged
WHERE RowNumber > ((@PageNumber - 1) * @PageSize) AND RowNumber <= (@PageNumber * @PageSize)
ORDER BY clausola_order

set @t2=getdate()
print (datediff(ms,@t1,@t2))
set @t1=getdate()

declare @tmp table (id int identity(1,1), key int, ...)
insert into @tmp
SELECT key, field_1,field_2, ... field_n
FROM table
WHERE (le_nostre_clausole_di_selezione)
ORDER BY clausola_order

SELECT key, field_1,field_2, ... field_n
FROM @tmp
WHERE id > ((@PageNumber - 1) * @PageSize) AND id <= (@PageNumber * @PageSize)

--drop table @tmp

set @t2=getdate()
print (datediff(ms,@t1,@t2))
set @t1=getdate()

create table #tmp (id int identity(1,1), key int, ...)
insert into #tmp
SELECT key, field_1,field_2, ... field_n
FROM table
WHERE (le_nostre_clausole_di_selezione)
ORDER BY clausola_order

SELECT key, field_1,field_2, ... field_n
FROM #tmp
WHERE id > ((@PageNumber - 1) * @PageSize) AND id <= (@PageNumber * @PageSize)

--drop table #tmp

set @t2=getdate()
print (datediff(ms,@t1,@t2))
set @t1=getdate()


Della prima esiste anche la variante senza CTE, ma il risultato non cambia:

SELECT key, field_1,field_2, ... field_n
FROM
(
    SELECT key, field_1,field_2, ... field_n, ROW_NUMBER() OVER (ORDER BY BET DESC) AS RowNumber
    FROM table
    WHERE (le_nostre_clausole_di_selezione)
    ORDER BY clausola_order
) a
WHERE RowNumber > ((@PageNumber - 1) * @PageSize) AND RowNumber <= (@PageNumber * @PageSize)

set @t2=getdate()
print (datediff(ms,@t1,@t2))
set @t1=getdate()

Ricordiamoci che la CTE è solo un alias per usare una stessa query in più punti: al posto di replicae del codice T-Sql, lo si scrive una volta per tutte. E' sempra la solita vecchia query innestata, in fondo.