¿Cuál es la mejor manera de paginar resultados en SQL Server?
¿Cuál es la mejor manera (en cuanto a rendimiento) de paginar resultados en SQL Server 2000, 2005, 2008, 2012 si también desea obtener el número total de resultados (antes de paginar)?
Finalmente, se lanzó Microsoft SQL Server 2012 , me gusta mucho su simplicidad para una paginación, no es necesario utilizar consultas complejas como las que se responden aquí.
Para obtener las siguientes 10 filas simplemente ejecute esta consulta:
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows- regresó
Puntos clave a considerar al usarlo:
ORDER BYEs obligatorio utilizarOFFSET ... FETCHla cláusula.OFFSETLa cláusula es obligatoria conFETCH. No puede utilizarORDER BY ... FETCH.TOPno se puede combinar conOFFSETyFETCHen la misma expresión de consulta.
Obtener el número total de resultados y paginar son dos operaciones diferentes. Por el bien de este ejemplo, supongamos que la consulta que está tratando es
SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
En este caso, determinaría el número total de resultados utilizando:
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
...lo que puede parecer ineficiente, pero en realidad tiene un rendimiento bastante bueno, suponiendo que todos los índices, etc., estén configurados correctamente.
A continuación, para obtener resultados reales en forma paginada, la siguiente consulta sería la más eficiente:
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
Esto devolverá las filas 1 a 19 de la consulta original. Lo bueno aquí, especialmente para las aplicaciones web, es que no es necesario mantener ningún estado, excepto los números de fila que se devolverán.
Increíblemente, ninguna otra respuesta ha mencionado la forma más rápida de realizar la paginación en todas las versiones de SQL Server. Los desplazamientos pueden ser terriblemente lentos para un gran número de páginas, como se compara aquí . Existe una forma completamente diferente y mucho más rápida de realizar la paginación en SQL. Esto a menudo se denomina "método de búsqueda" o "paginación de conjunto de claves", como se describe en esta publicación de blog aquí .
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
El "predicado de búsqueda"
Los valores @previousScorey @previousPlayerIdson los valores respectivos del último registro de la página anterior. Esto le permite buscar la página "siguiente". Si la ORDER BYdirección es ASC, simplemente úsela >en su lugar.
Con el método anterior, no puede saltar inmediatamente a la página 4 sin haber obtenido primero los 40 registros anteriores. Pero a menudo, de todos modos, no querrás saltar tan lejos. En su lugar, obtiene una consulta mucho más rápida que podría recuperar datos en tiempo constante, dependiendo de su indexación. Además, sus páginas permanecen "estables", sin importar si los datos subyacentes cambian (por ejemplo, en la página 1, mientras usted está en la página 4).
Esta es la mejor manera de implementar la paginación cuando se cargan más datos de forma diferida en aplicaciones web, por ejemplo.
Tenga en cuenta que el "método de búsqueda" también se denomina paginación del conjunto de claves .
Registros totales antes de la paginación
La COUNT(*) OVER()función de ventana le ayudará a contar el número total de registros "antes de la paginación". Si está utilizando SQL Server 2000, tendrá que recurrir a dos consultas para el archivo COUNT(*).
