Microsoft SQL Server distingue entre paginar y paginación. Paginar se refiere a administrar cuellos de botella de memoria, mientras que paginación, el foco de este artículo, se refiere a dividir al conjunto de resultados de una consulta T-SQL en partes discretas. El concepto general de Paginación es prevalente en varias disciplinas profesionales (por ejemplo, el periodismo, la publicación, el desarrollo web, etc.). De acuerdo a Wikipedia Paginación es el proceso de dividir contenido (por ejemplo, resultados de búsquedas de sitios web, artículos de periódicos, etc.) en páginas separadas pero relacionadas. El mayor beneficio de tal división de contenido es usualmente una estructura más limpia y clara de contenido que mejora la experiencia de lectura (o navegación) para el consumidor de tal contenido.

Un ejemplo de paginación en los resultados de una búsqueda de sitios web es ilustrada en la Figura S1, que muestra los resultados del motor de búsqueda Microsoft Bing acerca de una búsqueda para “ApexSQL”. Los números en la parte de debajo de la imagen indican al usuario que hay más resultados de búsqueda respecto a “ApexSQL”. Depende entonces del usuario navegar a más de estas páginas de resultados de búsqueda.

Figura S1

Paginación en plataformas de bases de datos que no son de Microsoft

En la mayoría de las plataformas de bases de datos, la paginación se refiere a reducir el nçumero de registros desde los conjuntos de resultados de consultas de un lenguaje de consultas estructurado (SEQUEL o SQL). De forma similar, la implementación de paginación ha sido principalmente consistente a través de las plataformas de bases de datos. Por ejemplo, la mayoría de las plataformas de bases de datos implementan paginación de conjuntos de resultados SQL usando las cláusulas OFFSET/FETCH y LIMIT.

La siguiente sub sección provee ejemplo de cómo las cláusulas OFFSET/FETCH y LIMIT son usadas en plataformas de bases de datos como DB2 de IBM, MySQL, Oracle y PostgreSQL.

1. Paginación usando la cláusula OFFSET/FETCH en Oracle y DB2

   La Figura S2 muestra un ejemplo de paginación de una consulta SQL en Oracle:

   Figura S2
   

   DB2 tiene una implementación diferente de paginación: un método es especificar la cláusula OFFSET (por ejemplo, como se muestra en la Figura S3), mientras que otro método es usar sólo la cláusula LIMIT con dos valores separados por comas, como se muestra en la Figura S4:

   Figura S3

   Figura S4

2. Paginación usando la cláusula LIMIT en MySQL y PostgreSQL

   La Figura S5 muestra un ejemplo de paginación de una consulta MySQL:

   Figura S5
   

   Finalmente, la Figura S6 muestra un script de paginación de muestra en PostgreSQL:

   Figura S6
   

Paginación en Microsoft SQL Server

A diferencia de otras plataformas de bases de datos que convenientemente administran la paginación a través de cláusulas OFFSET/FETCH y LIMIT, usted ha tenido que saltar a través de muchos (por ejemplo, usar funciones de ranking) para obtener la paginación de conjuntos de resultados de consultas para trabajar en SQL Server. La siguiente sub sección da un vistazo más cercano a algunas de las opciones de solución alternativa de paginación que han estado implementadas en SQL Server previamente a la versión SQL Server 2012.

1. Solución alternativa de paginación en Versiones de SQL Server previas a SQL Server 2012

   Una solución alternativa popular para paginar conjuntos de resultados en SQL Server ha sido usar la función ROW_NUMBER La función ROW_NUMBER, como muchos de sus parientes (por ejemplo, RANK, etc.), es una función de ventana que retorna un valor entero secuencial por fila de una partición dada de un conjunto de resultados. La función de ventanas fue introducida en la revisión ISO SQL:2003 del lenguaje de consultas de bases de datos SQL. La Figura S7 muestra una consulta T-SQL que retorna las primeras 10 filas desde la tabla SalesOrderDetail de la base de datos de muestra AdventureWorks2012.

   Figura S7
   

2. Paginación en SQL Server 2012 Usando el Filtro OFFSET-FETCH

   El filtro OFFSET-FETCH es la implementación de Microsoft de la cláusula estandarizada FETCH FIRST ISO SQL:2008 La cláusula estandarizada funciona limitando el conjunto de resultados en una consulta SQL dada. Previo a la introducción del filtro OFFSET-FETCH, Microsoft había introducido la cláusula TOP para limitar el conjunto de resultados de una consulta T-SQL. La sintaxis para usar la cláusula OFFSET-FETCH es como sigue:

   Figura S8
   

   Un detalle de la descripción de los parámetros del argumento [<offset_fetch>] está disponible en al sitio Microsoft TechNet.

   Las cláusulas OFFSET y FETCH tienen diferentes usos en el filtro OFFSET-FETCH. Por ejemplo, el argumento OFFSET es usado para indicar el número de filas a omitir, mientras que la cláusula FETCH es usada para indicar el número de filas que deberían ser recuperadas después de que OFFSET es aplicada. La Figura S9 muestra una consulta T-SQL que extrae las 10 primeras fillas de la tabla SalesOrderDetail.

   Figura S9
   

   De forma similar a las funciones T-SQL, siempre hay términos y condiciones para usar exitosamente el filtro OFFSET-FETCH. Algunas de las reglas que usted debería tener en cuenta cuando aplique el filtro OFFSET-FETCH son como sigue:

   1. Regla Número 1: Usted no puede hacerlo de las dos maneras

      Dado que la cláusula OFFSET-FETCH fue introducida como una alternativa a la cláusula TOP, tiene completo sentido entonces que no se le permita tener las cláusulas OFFSET-FETCH y TOP en la misma consulta T-SQL.

      Si usted decide ir adelante (por tanto, desafiando la Regla Número 1) e implementar las cláusulas OFFSET-FETCH y TOP en la misma consulta T-SQL, entonces es posible que emerja un mensaje de error como se muestra en la Figura S10.

      Figura S10
      

   2. Regla Número 2: Preocúpese de sus Tipos de Datos

      Si usted desea paginar exitosamente sus resultados en SQL Server 2012, entonces usted necesita cumplir con el uso de los tipos de datos: los valores enteros o de expresión para los argumentos de OFFSET no pueden ser menores que cero (por ejemplo, números negativos) y los valores enteros o de expresión para FETCH no pueden ser menores que uno (1).

      La Figura S11 ilustra qué pasaría cuando valores enteros incorrectos son provistos en un filtro OFFSET-FETCH. En este ejemplo, pedí a SQL Server que saltara menos una fila (-1).

      Figura S11
      

      En la Figura S12, pedí a SQL Server que saltara las primeras 5 filas y luego recuperara nada (cero filas). SQL Server reacciona y me pregunta por qué quisiera retornar cero filas, especialmente después de que había pedido saltar las primeras 5 filas.

      Figura S12
      

   3. Regla Número 3: Usted no puede tener uno sin el otro

      Para decodificar exitosamente una consulta T-SQL con un filtro OFFSET-FETCH, las cláusulas OFFSET y FETCH deberían ser incluidas en la consulta dada. En escenarios donde no se pretende omitir filas, cero (0) debería ser provisto junto con el argumento OFFSET. Por ejemplo, la Figura S13 muestra una implementación incorrecta al recuperar 10 registros sin saltar ningún registro, mientras que la Figura S14 muestra la implementación correcta para recuperar 10 registros in saltar ningún registro.

      Figura S13

      Figura S14

   4. Regla Número 4: Sin ORDER, no hay nada

      Si usted desea paginar exitosamente sus resultados en SQL Server 2012, entonces necesita incluir ORDER por cláusula. La Figura S15 muestra el mensaje de error que SQL Server 2012 retornará cuando sea que una cláusula ORDER BY es omitida en una consulta T-SQL que debería paginar un conjunto dado de resultados.

      Figura S15
      

   5. Regla Número 5: Conozca su ORDER

      El filtro OFFSET-FETCH siempre debería suceder a la cláusula ORDER BY y no al revés. La Figura S16 muestra el mensaje de error que SQL Server 2012 retornará cuando sea que una cláusula ORDER BY es forzada a suceder el filtro OFFSET-FETCH en una consulta T-SQL que debería paginar un conjunto dado de resultados.

      Figura S16
      

   6. Regla Número 6: Estas Cláusulas Intercambiables Aún Hacen Lo Mismo

      El filtro OFFSET-FETCH no dicta si el argumento ROW o ROWS debería ser usado – cualquiera de los argumentos mencionados son válidos. De todos modos, sería gramáticamente correcto usar ROW en lugar de ROWS cuando usted intenta recuperar/saltar una (1) fila. Tanto la sintaxis usada en la Figura S17 y como en la Figura S18 es correcta, pero si usted está muy pedante acerca del uso de gramática inglesa en T-SQL – la sintaxis mostrada en la Figura S18 debería ser la más deseable para usted.

      Figura S17

      Figura S18

      Además, el uso de las cláusulas FIRST y NEXT es intercambiable en la consulta T-SQL con filtro OFFSET-FETCH. Consecuentemente, ambas sintaxis en la Figura S19 y la Figura S20 son válidas.

      Figura S19
      

      Figura S20
      

El plan de ejecución de consultas de un conjunto de resultados de una consulta T-SQL

En SQL Server, el plan de ejecución de consultas ilustra los pasos tomados por el optimizador de consultas de SQL Server para ejecutar una consulta T-SQL dada. En esta sección daremos un vistazo al plan de ejecución de consultas T-SQL que usan el filtro OFFSET-FETCH versus las consultas que usan las cláusulas TOP y ROW_NUMBER. Para propósitos de esta demostración de ejecución de consultas, he iniciado por limpiar todas las entradas existentes de caché. SQL Server 2012 permite el uso de Database Console Command (DBCC) para limpiar la caché. La Figura S21 muestra el script de ejemplo que hemos corrido para limpiar toda la caché del plan.

Figure S21

1. El Plan de Ejecución de un Conjunto de Resultados de Consultas T-SQL Paginadas: Filtro OFFSET-FILTER vs cláusula TOP

   ¿Recuerda que OFFSET-FETCH fue introducido en SQL Server 2012 como una alternativa contra el uso de la cláusula TOP? Así que, ¿cómo maneja SQL Server una consulta T-SQL que usa una cláusula TOP versus un T-SQL que usa la cláusula OFFSET-FETCH? Bueno, parecería que el plan de ejecución de una consulta T-SQL que usa estas cláusulas limitantes (por ejemplo, TOP y OFFSET-FETCH) es el mismo.

   La Figura S22 y la Figura S23 ilustran los planes de ejecución para consultas T-SQL que usan cláusulas TOP y OFFSET-FETCH, respectivamente. Usted notará que pasos similares son tomados para retornar un conjunto de resultados – a pesar del hecho de que una consulta usa la cláusula TOP y la otra usa la cláusula OFFSET-FETCH.

   Figure S22
   

   Figure S23
   

2. El Plan de Ejecución de un Conjunto de Resultados de Consultas T-SQL Paginadas: filtro OFFSET-FILTER vs cláusula ROW_NUMBER

   A diferencia de los planes de ejecución similares de OFFSET-FETCH vs la cláusula TOP, el plan de ejecución para una consulta T-SQL que usa el filtro OFFSET-FETCH es diferente de una consulta T-SQL que usa la función de ranking ROW_NUMBER. La Figura S24 y la Figura S25 muestran las consultas T-SQL de paginación que han sido implementadas vía las cláusulas ROW_NUMBER y OFFSET-FETCH respectivamente. Las consultas están básicamente retornando las primeras 10001 filas después de saltar las primeras 100000 filas.

   Figure S24
   

   Figure S25
   

   Previamente a ver los planes de ejecución de ambas consultas, he analizado las estadísticas de IO y Time statistics para ambas consultas. La Figura S26 y la Figura S27 muestran que para ambas consultas, SQL Server lee páginas de datos desde la caché (lectura lógica) en lugar del disco duro (lectura física). La Figura S26 muestra que la consulta de paginación T-SQL que usa el filtro OFFSET-FETCH realiza menos lecturas lógicas para extraer los datos desde el objeto SalesOrderDetail (por ejemplo, 15 lecturas lógicas) en comparación a la consulta que usa la función de ranking (por ejemplo, 1371 lecturas lógicas). Notablemente también, hay diferencias en el conteo de escaneos que son hechos: el plan de la consulta que usa la función de ranking causa que el objeto SalesOrderDetail sea leído 5 veces en comparación a un solo escaneo que es realizado por la consulta que usa el filtro OFFSET-FETCH.

   Figura S26
   

   Figura S27
   

   Finalmente, la Figura S28 muestra que el plan gráfico de ejecución for paginating a T-SQL query result set that uses the OFFSET-FETCH filter is significantly different from the plan (Figure S29a and Figure S29b – the image of the plan was dissected into two parts so to fit into the width of this article) used for the result set that uses the ROW_NUMBER ranking function. Some of the significant differences are as follows:para paginar el conjunto de resultados de la consulta T-SQL que usa el filtro OFFSET-FETCH es significativamente diferente del plan (Figura S29a y Figura S29b – la imagen del plan fue diseccionada en dos partes de modo que quepa en el ancho de este artículo) usado para el conjunto de resultados que usa la función de ranking ROW_NUMBER. Algunas de las diferencias significativas son las siguientes:

   1. La primera cosa que destaca entre los dos planes gráficos de ejecución es el número total de íconos generados por consulta. Habían más íconos gráficos (por tanto, más operaciones) generados para ejecutar una consulta que usa la cláusula ROW_NUMBER comparada con la que usa el filtro OFFSET-FETCH. Recuerde, y como Tim Ford remarca este punto – A pesar de que la mayor parte de las operaciones en la consulta que usa la cláusula ROW_NUMBER tienen un costo de cero por ciento – aún había algún tiempo incurrido para llevar a cabo esas operaciones.
   2. El escaneo de índices agrupados realizado por el plan que usa la cláusula OFFSET-FETCH cuesta 2% menos que uno conducido por la función ROW_NUMBER.
   3. El 16 por ciento de todo el plan de ejecución para la consulta que usa el filtro OFFSET-FETCH fue a la operación del plan de consultas paralelo (por ejemplo, Gather Streams) mientras que la otra consulta incurrió en menos costos de paralelismo.

   4. Para ambas consultas, la mayoría de los costos fueron incurridos durante el ordenamiento de los conjuntos de resultados. De todas maneras, la consulta que usa la cláusula ROW_NUMBER incurrió en 12 por ciento más para ordenar los conjuntos de resultados en comparación con la otra consulta.

      Figura S28
      

      Figura S29a
      

      Figura S29b
      

Conclusión

La noción de paginación ha estado presente en muchas formas a través de diferentes cuerpos profesionales. En el Lenguaje de Consultas Estructurado, el estándar ISO en SQL ha provisto algún nivel de consistencia en cuanto se refiere a la implementación de paginación a través de plataformas de bases de datos. Paginar un conjunto de resultados de una consulta T-SQL en SQL Server 2012 ha provisto una alternativa mucho más flexible a la cláusula TOP. De todos modos, para una implementación exitosa de la característica de paginación en SQL Server 2012, las reglas que gobiernan el uso del filtro OFFSET-FETCH deberían ser obedecidas. Finalmente, en términos del plan de ejecución, SQL Server 2012 maneja las consultas que implementan el filtro OFFSET-FETCH de manera similar a aquellas consultas T-SQL que usan la cláusula TOP, pero el costo de una consulta que usa el filtro OFFSET-FETCH es significativamente menos comparado a una consulta T-SQL de paginación que usa la función de ranking ROW_NUMBER

Referencias:
ROW_NUMBER (Transact-SQL)
OFFSET FETCH Clause (SQL Server Compact)
Select (SQL) – ROW_NUMBER() window function
DBCC FREEPROCCACHE (Transact-SQL)
Gather Streams Showplan Operator
Clustered Index Scan Showplan Operator