La paginazione dei dati

pubblicato il 04/05/2010

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.