La paginación de datos desde el propio motor es una técnica que se utiliza en algunos procesos para poder llevar a la aplicación una cantidad reducida de registros y de ser necesarios mas saltar a una pagina posterior.
Para poder lograr esto han existido durante muchos años distintas técnicas de programación a nivel TSQL.
Ahora en SQL 2012 se ha incorporado la paginación de forma nativa la cual es parte del ANSI SQL 2000.
Para ello se utiliza dentro del Order By el seteo de OFFSET, FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY.
El siguiente link tiene información detallada de como se utiliza.
El siguiente código traerá los primeros 10 registros de un set de datos.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;
Bien ahora veremos un ejemplo de como se aplica la paginación antes de SQL 2012 y con esta nueva versión.
Para usar paginación antes de SQL 2012 una de las técnicas mas utilizadas es el uso de las CTE y Row_Number()
DECLARE @pagesize AS INT, @pagenum AS INT;
SET @pagesize = 5;
SET @pagenum = 1;
WITH SalesRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY Name) AS rownum,
ProductNumber, ProductID,Name
FROM[Production].[Product]
)
SELECT rownum, ProductNumber, ProductID,Name
FROM SalesRN
WHERE rownum > @pagesize * (@pagenum-1)
AND rownum <= @pagesize * @pagenum
ORDER BY rownum;
go
Ahora bien, si queremos hacer la misma query con SQL 2012, podríamos escribir lo siguiente
DECLARE @PageSize TINYINT = 5,
@CurrentPage INT = 1;
SELECT ProductNumber, ProductID,Name
FROM[Production].[Product]
ORDER BY Name
OFFSET (@PageSize * (@CurrentPage - 1))
ROWS
FETCH NEXT @PageSize ROWS ONLY;
Como se puede observar es mucho mas simple y natural ya que respeta a un estándar como el ANSI y la otra solución es un simple truco para lograr esto.
Veamos que sucede con los Query Plan de cada uno
Como se podrá observar en ambos casos es necesario hacer un Scan del Clustered pero al hacerlo con las nuevas instrucciones tenemos menos procesos y esto podría tener efectos del tamaño de una tabla.
Aquí comparamos ambos query plan de forma conjunta.
Ahora haremos una query que contemple paginación y el uso de Where, el primer ejemplo es antes de SQL 2012 y el segundo usando SQL 2012.
DECLARE @pagesize AS INT, @pagenum AS INT;
SET @pagesize = 5;
SET @pagenum = 1;
WITH SalesRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY Name) AS rownum,
ProductNumber, ProductID,Name
FROM[Production].[Product]
WHERE Color = 'Red'
)
SELECT rownum, ProductNumber, ProductID,Name
FROM SalesRN
WHERE rownum > @pagesize * (@pagenum-1)
AND rownum <= @pagesize * @pagenum
ORDER BY rownum;
DECLARE @PageSize TINYINT = 5,
@CurrentPage INT = 1;
SELECT ProductNumber, ProductID,Name
FROM [Production].[Product]
where Color = 'Red'
ORDER BY Name
OFFSET (@PageSize * (@CurrentPage - 1))
ROWS
FETCH NEXT @PageSize ROWS ONLY;
Filter by APML
Suscribirse