La paginazione dei dati in una query Sql

Innanzitutto chiarisco cosa intendo per paginazione dei dati. Supponiamo di avere una tabella contenente troppi record per poterli presentare tutti contemporaneamente, anche se applicassimo alcuni criteri di filtro: risulterebbe comodo in questo caso poter suddividere i dati (filtrati o meno) in “pagine”, ciascuna delle quali contenente un numero ristretto di record, ad esempio 50. Il vantaggio starebbe nel fatto che, invece di un unica estrazione con la totalità dei dati, potremmo suddividerla in diverse estrazioni, più leggere, ciascuna con un set limitato di dati (e quindi anche un minor peso sulle prestazioni generali). Per fare ciò, vi sono due tecniche, la prima delle quali è utilizzabile anche con il “vecchio” Ms Sql Server 2000, l’altra solo con le più recenti versioni dal 2005 in poi.

In entrambi i casi userò una tabella tblClienti, avente i campi IdCliente, Cognome, Nome e FlCancellazione; quest’ultimo è un campo bit, che vale 0 (False) se il record non è cancellato e quindi è attivo, mentre vale 1 (True) se il record è cancellato. Inoltre, in entrambi i casi creerò una stored procedure Client_PagedLoad i cui parametri saranno @PageIndex (il numero della pagina desiderata), @PageSize (il numero di record contenuti in ciascuna pagina), @IncludeDeletedRecord (indica se i record contrassegnati come cancellati devono essere inclusi nelle pagine restituite oppure no) e @Parameters (eventuali parametri aggiuntivi da specificare come filtro nella clausola WHERE.

In ogni caso non è molto complicato estendere l’uso anche a casi più complessi, magari con qualche tabella in join.

Sql Server 2000

In questo caso la tecnica prevede l’uso di una tabella temporanea ove memorizzare le chiavi primarie dei record, già filtrati sulla base del parametro @IncludeDeletedRecord e degli eventuali parametri aggiuntivi.

CREATE PROCEDURE Clienti_PagedLoad
(
    @PageIndex int,
    @PageSize int,
    @IncludeDeletedRecord bit,
    @Parameters varchar(200)
)
AS
    SET NOCOUNT ON

-- ricavo gli estremi inferiore e superiore
-- la prima pagina è 1
    DECLARE @Upper int
    DECLARE @Lower int

    SET @Lower = @PageSize * (@PageIndex - 1)
    SET @Upper = @Lower + @PageSize

-- tabella temporanea
    CREATE TABLE #Paging
    (
        RowID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
        IdCliente int NOT NULL
    )

-- inserisco la chiave della tabella in quella di appoggio
    DECLARE @SQL nvarchar(600)

    SET @SQL =
       'INSERT INTO #Paging(IdCliente) SELECT Id_Cliente
        FROM tblClienti
        WHERE FlCancellazione = CASE WHEN ' + CAST( @IncludeDeletedRecord as varchar(1) ) + ' = 0 THEN 0 ELSE FlCancellazione END '
                         + @Parameters + '
        ORDER BY Cognome, Nome ASC'

    EXEC(@SQL)

-- estraggo i dati dalle due tabelle
    SELECT tblClienti.*
    FROM #Paging
    INNER JOIN tblClienti ON #Paging.IdCliente = tblClienti.IdCliente
    WHERE #Paging.RowID > @Lower AND #Paging.RowID <= @Upper
    ORDER BY tblClienti.Cognome, tblClienti.Nome

    DROP TABLE #Paging

    SET NOCOUNT OFF

GO

Sql Server 2005/2008

In questo caso la tecnica prevede l’uso delle Common Table Expressions (o CTE) e della nuova funzione ROW_NUMBER(). La nuova stored procedure verrà quindi scritta così:

CREATE PROCEDURE Clienti_PagedLoad
(
    @PageIndex int,
    @PageSize int,
    @IncludeDeletedRecord bit,
    @Parameters varchar(200)
)
AS
    SET NOCOUNT ON

-- ricavo gli estremi inferiore e superiore
-- la prima pagina è 1
    DECLARE @Upper int
    DECLARE @Lower int
    DECLARE @statement nvarchar(4000)

    SET @Upper = @PageIndex * @PageSize
    SET @Lower = @Upper - @PageSize

    IF NOT RTRIM( @Parameters ) = ''
        IF NOT LEFT ( LTRIM( @Parameters ) , 3 ) = 'AND'
          SET @Parameters = ' AND ' + @Parameters

    SET @statement =
       'SELECT * FROM
        ( SELECT ROW_NUMBER() Over ( ORDER Cognome, Nome ) AS RowNum, *
          FROM tblClienti
          WHERE ( Fl_Cancellazione = CASE WHEN '+ CAST( @IncludeDeletedRecord as varchar( 1 ) )+' = 0 THEN 0 ELSE Fl_Cancellazione END )'
          + @Parameters + '
        ) Source
        WHERE ( RowNum > ' + Cast( @Lower AS Varchar(10) ) + ' )
          AND ( RowNum <= ' + Cast( @Upper AS Varchar(10) ) + ' )'
        +' ORDER BY Cognome, Nome'

     EXECUTE sp_executesql @statement

     SET NOCOUNT OFF
GO

La SELECT più interna restituisce l’elenco completo dei clienti, ordinati per Cognome e Nome ed eventualmente filtrati dalla clausola indicata in @Parameters; l’elenco viene però preceduto dal numero di riga, restituito dalla funzione ROW_NUMBER() introdotta in Sql Server 2005.

La SELECT esterna, invece è quella che effettivamente restituisce la pagina desiderata, filtrando, tra le righe restituite dalla query interna, quelle aventi il campo RowNumber (numero di riga) compreso nel range indicato.

N.B.: in rete si possono trovare altri esempi, simili ma non identici a questo, e che sfruttando le CTE per restituire un insieme paginato di dati; questo è solo uno dei possibili modi di ottenere il risultato, senza avere la pretesa di essere migliore di altri, ma solo di servire adeguatamente allo scopo.