Paginazione in T-SQL con SQL Server 2012

Normalmente nelle applicazioni web i dati vengono presi da SQL e presentati paginati.
Su grosse tabelle la paginazione deve assolutamente essere fatta lato server per evitare di trasferire grosse quantità di dati per poi visualizzarne un piccolo sottoinsieme.

SQL Server 2012 mette a disposizione le nuove keyword OFFSET e FETCH per gestire la paginazione.

Questo è un esempio dove nella variabile @startIndex indico il record da cui partire e in @pageSize il numero di record da ritornare.

SQLCopia

DECLARE @startIndex int = 0;  -- numero di riga da cui INIZARE (0=primo record)
DECLARE @pageSize int = 10;  -- numero di RIGHE da ritornare

SELECT * 
FROM [AdventureWorks2012].[Person].[Person] 
ORDER BY [FirstName]
OFFSET @startIndex  ROWS 
FETCH NEXT @pageSize ROWS ONLY;

Se invece devo ragionare in termini di numero pagina e non numero record, questo è l'esempio:

SQLCopia

DECLARE @pageNumber int = 1;  -- numero pagina da 1 a N
DECLARE @pageSize int = 10;

IF @pageNumber <=0
  SET @pageNumber =1

-- calcolo lo startIndex in funzione del numero pagina e degli elementi presenti nella pagina
DECLARE @startIndex int = (@pageNumber -1)*@pageSize ;

SELECT *
FROM [AdventureWorks2012].[Person].[Person] 
ORDER BY [FirstName]
OFFSET @startIndex  ROWS 
FETCH NEXT @pageSize ROWS ONLY;

il tutto può essere incluso in una store procedure aggiungendo anche un parametro per il sort:

SQLCopia

CREATE PROCEDURE SpuTodosSearch
(
  @pageNumber int = 1,
  @pageSize int = 10,
  @sort nvarchar(50) = 'ID DESC'

)
AS
BEGIN
  SET NOCOUNT ON
  IF @pageNumber <=0
    SET @pageNumber =1

  -- calcolo lo startIndex in funzione del numero pagina e degli elementi presenti nella pagina
  DECLARE @startIndex int = (@pageNumber -1)*@pageSize ;

  SELECT *
  FROM [AdventureWorks2012].[Person].[Person] 
  ORDER BY 
	CASE WHEN @sort='FirstName' THEN [FirstName] END,
	CASE WHEN @sort='FirstName desc' THEN [FirstName] END DESC,
    CASE WHEN @sort='ModifiedDate' THEN [ModifiedDate] END,
    CASE WHEN @sort='ModifiedDate desc' THEN [ModifiedDate] END DESC
  OFFSET @startIndex  ROWS 
  FETCH NEXT @pageSize ROWS ONLY;
END

che può essere richiamata con

SQLCopia

EXEC @return_value = [dbo].[uspTodosSearch]
     @pageNumber = 1,
     @pageSize = 10,
     @sort = N'modifieddate desc'