La paginación es un proceso que se utiliza para dividir un dato grande en páginas discretas más pequeñas, y este proceso también se conoce como paginación. La paginación es comúnmente utilizada por las aplicaciones web y se puede ver en Google. Cuando buscamos algo en Google, muestra los resultados en la página separada; esta es la idea principal de la paginación.
Ahora, vamos a discutir cómo lograr la paginación en SQL Server en las siguientes partes del artículo.
Preparación de datos de muestra
Antes de empezar a entrar en detalle sobre la paginación, crearemos una tabla de muestra y la poblaremos con algunos datos sintéticos. En la siguiente consulta, crearemos una tabla SampleFruits que almacena los nombres de las frutas y los precios de venta. En la siguiente parte del artículo, utilizaremos esta tabla.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE TABLE SampleFruits (
Id INT PRIMARY KEY IDENTITY(1,1) ,
FruitName VARCHAR(50) ,
Price INT
)
GO
INSERT INTO SampleFruits VALUES(‘Apple’,20)
INSERT INTO SampleFruits VALUES(‘Apricot’,12)
INSERT INTO SampleFruits VALUES(‘Banana’,8)
INSERT INTO SampleFruits VALUES(‘Cherry’,11)
INSERT INTO SampleFruits VALUES(‘Strawberry’,26)
INSERT INTO SampleFruits VALUES(‘Lemon’,4)
INSERT INTO SampleFruits VALUES(‘Kiwi’,14)
INSERT INTO SampleFruits VALUES(‘Coco’,34)
INSERT INTO SampleFruits VALUES(‘Naranja’,24)
INSERT INTO SampleFruits VALUES(‘Frambuesa’,13)
INSERT INTO SampleFruits VALUES(‘Mango’,9)
INSERT INTO SampleFruits VALUES(‘Mandarin’,19)
INSERT INTO SampleFruits VALUES(‘Pineapple’,22)
GO
SELECT * FROM SampleFruits
|
¿Qué es la Paginación en SQL Server?
En términos de SQL Server, el objetivo de la paginación es, dividir un conjunto de resultados en páginas discretas con la ayuda de la consulta. Cuando los argumentos OFFSET y FETCH se utilizan con la cláusula ORDER BY en una sentencia SELECT, será una solución de paginación para SQL Server.
El argumento OFFSET especifica cuántas filas se saltarán del conjunto de resultados de la consulta. En el siguiente ejemplo, la consulta omitirá las 3 primeras filas de la tabla SampleFruits y luego devolverá todas las filas restantes.
|
1
2
3
4
|
SELECT NombreFruta, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Cuando establecemos el valor OFFSET como 0, no se omitirá ninguna fila del conjunto de resultados. La siguiente consulta puede ser un ejemplo de este tipo de uso:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
Por otro lado, si establecemos el valor OFFSET, que es mayor que el número total de filas del conjunto de resultados, no se mostrará ninguna fila en el resultado. Si consideramos la siguiente consulta, el número total de filas de la tabla SampleFruits es 13, y establecemos el valor OFFSET como 20, por lo que la consulta no mostrará ningún resultado.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
El argumento FETCH especifica cuántas filas se mostrarán en el resultado, y el argumento FETCH debe utilizarse con el argumento OFFSET. En el siguiente ejemplo, omitiremos las primeras 5 filas y luego limitaremos el conjunto de resultados a 6 filas para nuestra tabla de muestra.
|
1
2
3
4
|
SELECT NombreFruta, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip: La CLÁUSULA TOP limita el número de filas que devuelve la sentencia SELECT. Cuando utilizamos la cláusula TOP sin ORDER BY, se pueden devolver resultados arbitrarios. Si consideramos el siguiente ejemplo, devolverá 3 filas aleatorias en cada ejecución de la consulta.
|
1
2
|
SELECT TOP 7 FruitName, Price
FROM SampleFruits
|
Como hemos aprendido, el argumento OFFSET-FETCH requiere la cláusula ORDER BY en la sentencia SELECT. Si queremos implementar un orden indefinido que se parezca al uso anterior de la cláusula TOP con argumentos OFFSET-FETCH, podemos utilizar una consulta como la siguiente
|
1
2
3
|
SELECT NombreFruta ,Price FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
Consulta de paginación en SQL Server
Después de averiguar la respuesta a «¿Qué es la paginación?», aprenderemos cómo podemos escribir una consulta de paginación en SQL Server. Al principio, ejecutaremos la siguiente consulta y abordaremos la consulta:
|
1
2
3
4
5
6
7
8
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=4
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Como podemos ver, hemos declarado dos variables en la consulta anterior, y estas variables son:
- @NúmeroDePágina – Especifica el número de la página que se mostrará
- @RowsOfPage – Especifica cuántos números de filas se mostrarán en la página. Como resultado, la sentencia SELECT muestra la segunda página, que contiene 4 filas
Ordenación dinámica con paginación
Las aplicaciones pueden necesitar ordenar los datos según diferentes columnas, ya sea en orden ascendente o descendente, además de la paginación. Para superar este tipo de requerimientos, podemos utilizar una cláusula ORDER BY con condiciones CASE de forma que obtengamos una consulta que pueda ser ordenada por las variables. La siguiente consulta puede ser un ejemplo de este tipo de uso:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @SortingCol AS VARCHAR(100) =’FruitName’
DECLARE @SortType AS VARCHAR(100) = ‘DESC’
SET @PageNumber=1
SET @RowsOfPage=4
SELECT FruitName,Price FROM SampleFruits
ORDER BY
CASE WHEN @SortingCol = ‘Price’ AND @SortType =’ASC’ THEN Price END ,
CASE WHEN @SortingCol = ‘Price’ AND @SortType =’DESC’ THEN Price END DESC,
CASE WHEN @SortingCol = ‘FruitName’ AND @SortType =’ASC’ THEN FruitName END ,
CASE WHEN @SortingCol = ‘FruitName’ AND @SortType =’DESC’ THEN FruitName END DESC
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Además, podemos cambiar la columna de ordenación y la dirección de ordenación a través de las variables para la consulta anterior.
Paginación en un bucle
En este ejemplo, aprenderemos una técnica de consulta que devuelve todos los resultados de páginas discretas con una sola consulta.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @MaxTablePage AS FLOAT
SET @PageNumber=1
SET @RowsOfPage=4
SELECT @MaxTablePage = COUNT(*) FROM SampleFruits
SET @MaxTablePage = CEILING(@MaxTablePage/@RowsOfPage)
WHILE @MaxTablePage >= @PageNumber
BEGIN
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
SET @PageNumber = @PageNumber + 1
END
|
Para esta consulta, creamos una fórmula bastante sencilla. En primer lugar, hemos asignado el número total de filas de la tabla SampleFruit a la variable @MaxTablePage, y luego lo hemos dividido entre el número de filas que se mostrarán en una página. Así, hemos calculado el número de páginas que se mostrarán. Sin embargo, el valor calculado puede ser un decimal, y para ello, utilizamos la función CEILING para redondearlo al número entero más pequeño que sea mayor que el número calculado. Como segundo paso, implementamos un WHILE-LOOP e iteramos la variable @PageNumber hasta la última página del número.
Conclusión
En este artículo, intentamos encontrar la respuesta a la pregunta «¿Qué es la Paginación?», particularmente para SQL Server. Los argumentos OFFSET-FETCH ayudan a implementar cuántas filas queremos omitir y cuántas filas queremos mostrar en el conjunto de resultados cuando los usamos con la cláusula ORDER BY en las sentencias SELECT. Y finalmente, aprendimos cómo podemos lograr la paginación en SQL Server con estos argumentos.
- Autor
- Postes recientes
La mayor parte de su carrera se ha centrado en la administración y desarrollo de bases de datos SQL Server. Sus intereses actuales son la administración de bases de datos y el Business Intelligence. Puedes encontrarlo en LinkedIn.
Ver todos los comentarios de Esat Erkec
- Mitos de la optimización de consultas – 23 de marzo, 2021
- Síntomas de la detección de parámetros en SQL Server – 17 de marzo de 2021
- Uso de la corrección automática del plan para el ajuste de consultas – 4 de marzo de 2021