se usa a menudo en aplicaciones donde el usuario puede hacer clic en Anterior/Siguiente para navegar por las páginas que componen los resultados, o hacer clic en un número de página para ir directamente a una página específica.
Al ejecutar consultas en SQL Server, puede paginar los resultados utilizando los argumentos OFFSET y FETCH de la cláusula ORDER BY. Estos argumentos se introdujeron en SQL Server 2012, por lo que puede usar esta técnica si tiene SQL Server 2012 o superior.
En este contexto, la paginación es donde se dividen los resultados de la consulta en trozos más pequeños, cada trozo continúa donde terminó el anterior. Por ejemplo, si una consulta devuelve 1000 filas, puede paginarlas para que se devuelvan en grupos de 100. Una aplicación puede pasar el número de página y el tamaño de página a SQL Server, y SQL Server puede usarlo para devolver solo los datos de la página solicitada.
Ejemplo 1-Sin paginación
Primero, ejecutemos una consulta que devuelva todas las filas de una tabla:
SELECT *FROM GenresORDER BY GenreId;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
Este ejemplo no utiliza paginación-se muestran todos los resultados.
Este conjunto de resultados es tan pequeño que normalmente no requeriría paginación, pero para los propósitos de este artículo, vamos a paginarlo.
Ejemplo 2-Mostrar los Primeros 3 Resultados
Este ejemplo muestra los tres primeros resultados:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
En este caso, especifico que los resultados deben comenzar en el primer resultado y mostrar las tres filas siguientes. Esto se hace utilizando lo siguiente:
-
OFFSET 0 ROWSespecifica que no debe haber desplazamiento (un desplazamiento de cero). -
FETCH NEXT 3 ROWS ONLYobtiene las tres filas siguientes del desplazamiento. Como he especificado un desplazamiento de cero, se obtienen las tres primeras filas.
Si todo lo que queríamos eran los 3 mejores resultados, podríamos haber logrado el mismo resultado utilizando la cláusula TOP en lugar de especificar los valores de desplazamiento y recuperación. Sin embargo, esto no nos habría permitido hacer la siguiente parte.
Ejemplo 3: Mostrar el Siguiente 3 Resultados
Ahora vamos a ver los siguientes tres resultados:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Así que lo único que cambió fue el desplazamiento.
Los valores de desplazamiento y obtención también pueden ser una expresión proporcionada como una subconsulta escalar variable, parámetro o constante. Cuando se utiliza una subconsulta, no puede hacer referencia a ninguna columna definida en el ámbito de la consulta externa (no se puede correlacionar con la consulta externa).
Los siguientes ejemplos usan expresiones para mostrar dos enfoques para paginar los resultados.
Ejemplo 4-Paginación por Número de fila
Este ejemplo utiliza expresiones para especificar el número de fila en el que comenzar.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Aquí, yo uso @StartRow int = 1 para especificar que los resultados deben iniciar en la primera fila.
Esto es lo que sucede si incremento ese valor a 2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
Se inicia en la segunda fila. Usando este método, puedo especificar la fila exacta en la que comenzar.
Ejemplo 5-Paginación por número de página
Este ejemplo es casi idéntico al ejemplo anterior, excepto que le permite especificar el número de página, en lugar del número de fila.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Así que el primer resultado es el mismo. Sin embargo, veamos qué sucede cuando incrementamos @PageNumber a 2 (cambié el nombre de esta variable para reflejar su nuevo propósito).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Esta vez los resultados comienzan en la cuarta fila. Por lo tanto, con este método, simplemente puede pasar el número de página en lugar del número de fila.
Ejemplo 6 – Bucle de paginación
Para terminar, aquí hay un ejemplo rápido que recorre todas las páginas y especifica el número de fila inicial para cada iteración:
DECLARE @StartRow int = 1, @RowsPerPage int = 3;WHILE (SELECT COUNT(*) FROM Genres) >= @StartRow BEGIN SELECT * FROM Genres ORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;SET @StartRow = @StartRow + @RowsPerPage; CONTINUEEND;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 7 | Rap || 8 | Punk |+-----------+---------+(2 rows affected)
Ejemplo 7 – FILA vs FILAS
Si encuentra código que usa ROW en lugar de ROWS, ambos argumentos hacen lo mismo. Son sinónimos y se proporcionan para la compatibilidad ANSI.
Este es el primer ejemplo de esta página, pero con ROW en lugar de ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
en el Ejemplo 8 – PRIMERA vs SIGUIENTE
lo mismo se aplica a FIRST y NEXT. Estos son sinónimos proporcionados para la compatibilidad ANSI.
Este es el ejemplo anterior, pero con FIRST en lugar de NEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+