Algunas palabras e ideas iniciales
En lo particular me encontré con esta necesidad desde los primeros desarrollos, en aquel tiempo utilizando Basic 4690 contra un archivo de texto, si mal no recuerdo, en esa ocasión eran un tipo de archivos que se denominaban keyed files, puede ser que la memoria me falle un poco, pero dejando detalles atrás lo importante es que desde entonces nunca más pude eludir esta tarea hasta la actualidad, consultando registros sobre una base de datos.
Estoy seguro, sin querer generalizar, que así debe ser para una gran parte de los desarrolladores. Por otra parte, si hablamos de reportes creo que no podemos olvidarnos hablar de paginado, ya que muchas veces ambos conceptos estarán relacionados, obviamente hay excepciones posiblemente cuando hablemos por ejemplo de exportación de datos, ya que será lo más probable que desearemos listar todos los datos, pero como eso le saca un poco de emoción a la tarea, en este articulo vamos omitir esta opción y nos vamos a dedicar a mencionar a aquellos reportes donde si es necesario paginar y nuestro soporte de datos será un servidor SQL Server 2005 o 2008 y luego un SQL Server 2012. Hablaremos de este servidor en particular por que en esta versión se han incluido algunas funciones que permitirán simplificar un poco la tarea.
Creo que está de más aclarar, como indica el titulo del artículo, que estamos considerando (sin importar desde donde consumiremos los datos) que el paginado de nuestras consultas se llevará a cabo en el servidor de base de datos, espero que las razones sean claras, siempre que sea posible es imprescindible paginar de lado del servidor de base de datos por razones de performance, para describirlo gráficamente, si Juan y Pedro me dan permiso para utilizar sus nombres, paso a comentarles que si Juan le pide un libro a Pedro, Pedro podría subir hasta la biblioteca ubicada en el décimo piso tomar el libro y bajarlo diez pisos hasta donde se encuentra Juan o también podría bajar diez pisos la biblioteca entera, con estantes y todo y luego buscar el libro en planta baja donde se encuentra Juan. En ambos casos Juan obtendrá el libro que necesita, pero en el segundo caso, deberá esperar más tiempo, el ascensor estará ocupado más tiempo, impidiendo el uso de otros empleados y Pedro posiblemente consumirá más tiempo, que podría utilizar para realizar otras tareas, y ni hablar que sucedería si además asumimos que la biblioteca va creciendo con el tiempo. Espero que con esta grotesca explicación quede claro el tema del paginado y por qué lo realizamos de lado del servidor SQL Server, lo más cerca posible de los datos.
Para quienes hayan intentado realizar esta tarea con SQL Server no 2012 sabrán que si bien no es difícil, tampoco es tan directo y para quienes incluso hayan tenido que hacerlo con SQL Server 7 o SQL Server 2000… bueno por suerte esto ya es historia antigua, pero recuerdo que la situación ya se tornaba algo intrincada.
SQL Server 2005 y 2008
En SQL Server 2005 y 2008 podíamos efectuar esta tarea apoyándonos en la ayuda de la función ROW_NUMBER() que había llegado con SQL Server 2005.
La función ROW_NUMBER() permite enumerar los registros en base a un criterio.
Para empezar a ver de que trata, podríamos consultar la tabla Product de la base de datos AdventureWorks:
De la siguiente forma:
Obtendremos un grupo de resultados.
Si luego quisiéramos enumerar los registros obtenidos podríamos incluir la función ROW_NUMBER() de la siguiente forma:
En el ejemplo anterior obtendremos el número de fila asociado a los registros obtenidos. Un detalle importantísimo es que la función ROW_NUMBER() utiliza su propio orden aislado de la consulta en la que se encuentra incluida, por ese motivo debemos agregar el campo y orden de filtrado y no es un simple ROW_NUMBER sin parámetros, en base a lo comentado podríamos modificar lo anterior y realizar la siguiente consulta:
En este último ejemplo queda absolutamente claro la independencia de la función ROW_NUMBER(), ya que la consulta sigue devolviendo los registros ordenados por el campo [Name] ASC, pero la función ROW_NUMBER() está devolviendo el número que le corresponderá al registro si el mismo es ordenado por el campo [ListPrice] DESC.
Con la ayuda de esta función es claro que podemos paginar por un criterio, a continuación y modificaremos un poco el ejemplo de la siguiente forma:
En el ejemplo de arriba hemos obtenido una página de resultados, correspondiente a los registros 55 al 60 con el reporte ordenado por las columnas [Color] ASC y [ListPrice] DESC (cambiamos el ordenamiento para demostrar que es posible utilizar combinaciones en forma sencilla y sin ningún problema), y además excluimos los productos donde el campo [Color] tome el valor “Silver”. No hay dudas que tenemos todos los elementos que podrá requerir un reporte, orden, filtros y paginado.
Un detalle a comentar es que pueden utilizarse Common Table Expressions (CTE) en remplazo de las tablas derivadas obteniendo el mismo resultado, vamos a dejar esta posibilidad fuera del alcance del artículo, pero las hemos utilizado y funcionan sin problemas.
SQL Sever 2012
SQL Server 2012 trae otra sentencia capaz de realizar una tarea similar en forma más sencilla, esta vez podemos empezar por escribir la consulta y luego comentar de que se trata.
Con la ayuda de la sentencia OFFSET e indicando la fila inicial (0) y el tamaño de la página (5), hemos obtenido el mismo resultado. Como contra (si es una contra) veremos claramente que ya no podemos separar la consulta del ordenamiento y a favor tenemos una sentencia más simple y que toma como parámetro el tamaño de la página, que para mí al menos es más cómodo y evita tener el agregado de algunos cálculos extra.
No quiero avanzar sin dejar un comentario final sobre la función ROW_NUMBER, esta función incluye posibilidades más amplias que las comentadas en este artículo hasta ahora, es posible por ejemplo, efectuar:
En este caso los resultados serán particionados por el campo [Size], el particionamiento reiniciará el contador de la función ROW_NUMBER(), tal como puede verse en el campo ROWNUMBER en el ejemplo previo.
Esta función forma parte de un grupo más amplio de funciones denominadas “Funciones de Ranking” incorporadas en SQL Server 2005. Todo esto va más allá de lo necesario a saber para el paginado de reportes, pero si alguien está interesado en conocer un poco más, les dejo este link Funciones de ranking (MSDN).
Ajustes finales y creación de un Stored Procedure
Con lo que hemos visto hasta ahora, ya sea que utilicemos la función ROW_NUMBER() o la sentencia OFFSET no será muy complejo crear un stored procedure que ajustando los parámetros adecuadamente permita obtener el reporte del cual estuvimos hablando desde el comienzo del articulo, avanzaremos entonces en este camino utilizando la segunda opción y veremos algunos detalles más.
Para no hablar demasiado y aburrir con explicaciones, crearemos ya mismo el Stored Procedure:
CREATE PROCEDURE Paging_test
@PageSize INT --Tamaño página
,@PageNumber INT --Número de página
,@PageCount INT OUT --Retorno de cantidad de páginas
,@Color NVARCHAR(15) = NULL --Filtro por campo Color
,@Size NVARCHAR(5) = NULL --Filtro por campo Size
AS
BEGIN
--Validación de precondiciones
SET @PageCount = 0
IF @PageSize < 1 OR @PageNumber < 1 RETURN
--Ajuste de cantidad de páginas
DECLARE @RecordCnt INT
SELECT
@RecordCnt = COUNT(*)
FROM
[SalesLT].[Product]
WHERE
(@Color IS NULL OR [Color]=@Color)
AND (@Size IS NULL OR [Size]=@Size)
IF @RecordCnt = 0
SET @PageCount = 0
ELSE IF @RecordCnt % @PageSize = 0
SET @PageCount = @RecordCnt / @PageSize
ELSE
SET @PageCount = (@RecordCnt / @PageSize) + 1
--Registros paginados,filtrados y ordenados
DECLARE @offset INT = (@PageSize * (@PageNumber - 1))
SELECT
[ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[ListPrice]
,[Size]
,[Weight]
FROM
[SalesLT].[Product]
WHERE
(@Color IS NULL OR [Color]=@Color)
AND (@Size IS NULL OR [Size]=@Size)
ORDER BY
Color ASC, ListPrice DESC
OFFSET @offset ROWS FETCH NEXT @PageSize ROWS ONLY;
END
El stored procedure Paging_test tomará por parámetros el tamaño de la página, la página pedida y los filtros, hará algunas validaciones y cálculos basicos. Luego devolverá la cantidad de páginas en un parámetro de salida junto con los registros paginados, filtrados y ordenados, si probamos invocar al procedimiento utilizando el filtro @Color veremos que todo funciona según lo esperado y que luego de un artículo de espera, ya hemos llegado a terminar nuestro objetivo.
DECLARE @PageCount int EXEC [Paging_test] @PageSize = 5, @PageNumber = 1, @PageCount = @PageCount OUTPUT, @Color = N'Silver' SELECT @PageCount as N'@PageCount'
En este caso lo hemos invocado desde el mismo SQL Server Management Studio, pero podremos hacerlo sin ningún problema por ejemplo desde C#.
Cierro el artículo, como siempre, esperando que sea de utilidad para las versiones SQL Server 2005 y 2008 y para la nueva versión 2012 de SQL Server. Cuando tengamos la posibilidad hablaremos un poco más de SQL Server 2012 y las novedades que trae.