SQL11 (Denali): OFFSET y FETCH (Nuevas opciones para ORDER BY)
En Denali se ha mejorado la funcionalidad de la cláusula ORDER BY agregando más argumentos en busca de dar solución a escenarios específicos como es el caso del paginado, es decir, simular la lectura por partes de los resultados de una consulta, tema que se podía realizar en versiones previas combinando TOP, OVER y ORDER BY, sin embargo, el rendimiento de esta solución es pobre comparado a lo que se puede conseguir con OFFSET y FETCH. En OFFSET se debe indicar el número de filas que debemos saltar antes de iniciar la lectura, en otras palabras, viene a ser la posición relativa donde debemos ubicar el puntero de lectura. Luego con FETCH indicamos la cantidad de filas que deseamos recorrer con la lectura. Para SQL Server, el valor que toma OFFSET viene a ser la posición cero, y luego hace un TOP de acuerdo al valor dado en el argumento FETCH. Ese es el funcionamiento interno de estas opciones, las cuales se basan siempre en el criterio de ordenamiento ya sea ascedente o descendente de ORDER BY.
Una recomendación en el uso de estas opciones es, en la medida posible (o mejor dicho siempre), trabajar con un índice (de preferencia clustered) sobre la columna que participa en la cláusula ORDER BY y ayudarse de filtros WHERE sobre dicha columna, de esta manera el tema será suficientemente escalable y así evitaremos costosas operaciones table scan o index scan. He realizado las pruebas sobre 10 millones de filas y la verdad que si responde bien (para empezar claro está, y usando clustered index como mínimo y a veces filtros).
Por ejemplo, en el siguiente ejemplo, ubicaremos el punto de inicio de lectura en la fila 4(posición cero para SQL Server), y se leerá todo el resto de filas a partir de la fila 5 (que para SQL Server será posición 1). La consulta devolverá 87 filas de un total de 91.
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../
FROM dbo.Customers
ORDER BY CustomerID
OFFSET (4) ROWS ---ó OFFSET (4) ROW
Ahora usando el criterio de ordenación DESC los resultados serán diferentes:
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../
FROM dbo.Customers
ORDER BY CustomerID DESC
OFFSET (4) ROWS ---ó OFFSET (4) ROW
Cada ejecución de la consulta de una página es independiente una de la otra, eso quiere decir que, si consultamos la primera página de 4 filas y luego otra de la misma cantidad (o diferente) ambas consultas no estarán relacionadas, el resultado se devuelve inmediatamente al cliente y se libera recursos del servidor, no se guarda el estado en el servidor como si suele pasar con un cursor el cual generalmente causa problemas de bloqueos y bastante consumo de recursos en muchos casos. Bien, si deseamos leer sólamente las 3 primeras o siguientes filas después del OFFSET:
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../
FROM dbo.Customers
ORDER BY CustomerID
OFFSET (4) ROWS ---ó OFFSET (4) ROW
FETCH NEXT 3 ROWS ONLY -- ó FETCH FIRST 3 ROWS ONLY
GO
Para leer las siguiente 3 filas simplemente cambiamos el valor del OFFSET:
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../
FROM dbo.Customers
ORDER BY CustomerID
OFFSET (7) ROWS ---ó OFFSET (7) ROW
FETCH NEXT 3 ROWS ONLY -- ó FETCH FIRST 3 ROWS ONLY
GO
La lectura de cada página de filas las podemos realizar dinámicamente usando variables y en algunos casos aplicando hint
OPTIMIZE FOR para particularizar la optimización cuando se use filtros específicos.
DECLARE @Start INT, @Next INT
SET @Start=0
SET @Next=3
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../
FROM dbo.Customers
ORDER BY CustomerID
OFFSET (@Start) ROWS
FETCH NEXT @Next ROWS ONLY
OPTION(OPTIMIZE FOR (@Start=4,@Next=5))
GO
Podemos usarlo dentro de un procedimiento almacenado:
CREATE PROCEDURE dbo.PageFetch(@PageNumber INT, @PageSize INT)
AS
BEGIN
SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS 'Nro',
CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../
FROM dbo.Customers
ORDER BY CustomerID
OFFSET (@PageSize * (@PageNumber -1) ) ROWS
FETCH NEXT @PageSize ROWS ONLY
END
Ahora podemos consultar las páginas de filas que deseemos, por ejemplo, a continuación la consulta de basa en páginas de 3 filas, y se leerá las páginas 2,3, y 5.
EXEC dbo.PageFetch 2,3;
GO
EXEC dbo.PageFetch 3,3;
GO
EXEC dbo.PageFetch 5,3;
OFFSET y FETCH se podrá usar dentro de vistas, subconsultas, funciones y tablas derivadas, sin embargo, también tiene sus limitaciones, una de ellas es que no pueden usarse dentro de vistas indexadas, o directamente con TOP, OVER, INSERT, UPDATE, MERGE, ó DELETE, pero si dentro de expresiones independientes a más bajo nivel, como subconsultas de las cuales se alimenten. Por ejemplo:
SELECT TOP(2) ROW_NUMBER() OVER(ORDER BY CustomerID) AS 'NRO', CompanyName, ContactName, ContactTitle FROM (
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../
FROM dbo.Customers
ORDER BY CustomerID
OFFSET (4) ROWS ---ó OFFSET (4) ROW
FETCH NEXT 3 ROWS ONLY -- ó FETCH FIRST 3 ROWS ONLY
) AS T1
Su uso con vistas:
CREATE VIEW dbo.v1
as
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address --/..*.../
FROM dbo.Customers
ORDER BY CustomerID
OFFSET (0) ROWS ---ó OFFSET (4) ROW
GO
SELECT CustomerID, CompanyName, ContactName FROM dbo.v1
WHERE CustomerID LIKE 'AN%'
En conclusión, OFFSET y FETCH son excelentes mejoras que facilitan implementar soluciones de paginado de resultados, sin embargo, para que sea escalable hay que usarlo en combinación con índices y filtros.
Más información:
ORDER BY Clause (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx
PercyReyes,