En este artículo exploramos las cláusulas OFFSET y FETCH. OFFSET y FETCH se utilizan junto con la cláusula SELECT STATEMENT ORDER BY para proporcionar un medio para recuperar un rango de registros. La fila inicial a devolver está determinada por el valor de DESPLAZAMIENTO y el número máximo de filas a devolver a partir de ese punto por FETCH.

Todos los ejemplos de esta lección se basan en Microsoft SQL Server Management Studio y la base de datos AdventureWorks2012. Puede comenzar a usar estas herramientas gratuitas utilizando mi guía Introducción a SQL Server.

Usar OFFSET y FETCH con la cláusula ORDER BY

Devolver filas de una instrucción SQL puede ser un asunto de todo o nada. En muchos casos, el número de filas devueltas es muy grande y esto puede causar problemas si solo necesita formar parte del conjunto de resultados.

Cuando los resultados se ordenan mediante la cláusula ORDER BY, entran en juego algunas opciones para limitar el número de filas devueltas:

1. Puede usar TOP para devolver un número especificado de filas.
2. Puede usar DESPLAZAMIENTO y BUSCAR.

En este artículo profundizamos en más información sobre el DESPLAZAMIENTO y la BÚSQUEDA. Para obtener más información sobre TOP, lea el artículo Introducción a SQL Server: 2. Ordena Los Resultados De Tu Consulta.

OFFSET

El argumento OFFSET se utiliza para identificar el punto de partida para devolver filas a partir de un resultado. OFFESET se llama argumento, ya que es técnicamente parte de la cláusula ORDER BY. El DESPLAZAMIENTO es el número de filas a omitir antes de incluirlas en el resultado.

La forma general para el argumento OFFSET es:

SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS

Donde las filas-a-skip es un valor mayor o igual a cero.

Por ejemplo, para mostrar todos los empleados, excepto los primeros 10, ordenados por HireDate, puede escribir

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS

Aquí hay algunas cosas a tener en cuenta sobre el DESPLAZAMIENTO

• El DESPLAZAMIENTO forma parte de la cláusula ORDER BY. No se puede usar solo.
• Los valores de desplazamiento deben ser cero o mayores. Un número negativo resulta en un error.
• Cuando el DESPLAZAMIENTO es 0, no se omiten filas.
• Si el DESPLAZAMIENTO es mayor que el número de filas en los resultados ordenados, no se devuelve ninguna fila.

FETCH

El argumento FETCH se utiliza para devolver un número determinado de filas. FETCH no se puede usar por sí solo, se usa junto con OFFSET.

Continuando con nuestro ejemplo, podemos mostrar a los empleados del 11 al 15 empleados contratados usando esta declaración

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

En el siguiente diagrama puede ver DESPLAZAMIENTO y BÚSQUEDA en el trabajo. OFFSET se utiliza para saltar las primeras 10 filas y FETCH se utiliza para mostrar las 5 siguientes.

La combinación de DESPLAZAMIENTO y RECUPERACIÓN facilita la recuperación de una ventana» deslizante » de filas. El inicio de la ventana está determinado por el DESPLAZAMIENTO y la altura por la BÚSQUEDA.

Usos para OFFSET y FETCH

Paginación

Uno de los usos más populares para OFFSET y FETCH es la paginación. Sin duda, ha visitado el sitio web donde ve una lista de elementos y en la parte inferior hay una lista de números de página o un botón siguiente.

Usamos paginación todo el tiempo en la web. El ejemplo más popular que se me ocurre es Google:

Los números debajo de Google representan números de página. Una parte de los resultados de búsqueda se devuelve con cada clic.

De manera similar, supongamos que tenemos una página web que muestra a los empleados por contrato. Si queríamos mostrar 20 empleados en una página, y estábamos mostrando la 3a página (empleados 21-30), podríamos usar la siguiente consulta:

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Esta consulta indica a SQL que:

1. Seleccione la información del empleado
2. Ordene la información por HireDate
3. Omita 20 filas y comience a mostrar los resultados desde la 21a
4. Muestre las siguientes 10 filas de resultados.

Obtener los registros superiores

Si desea obtener las diez primeras filas de una consulta, puede hacerlo configurando el DESPLAZAMIENTO a 0. Recuerde que el DESPLAZAMIENTO especifica el número de filas a omitir. Al establecerlo en cero, le decimos a SQL que comience en la primera fila.

Una vez establecido el DESPLAZAMIENTO, el siguiente orden de trabajo es establecer FETCH. Ya que estamos buscando los diez primeros, establecemos BUSCAR AL LADO de 10.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Si está familiarizado con la cláusula SUPERIOR, puede haber notado que hay similitudes entre estos dos métodos. Por ejemplo, en el ejemplo anterior, usar TOP se vería como

SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate

Hay aspectos en TOP que no se aplican a OFFSET y FETCH; como ser permitido en sentencias sin un ORDER BY, pero como puede ver en este ejemplo, devuelven resultados equivalentes.

Obtener los registros inferiores

Para obtener los registros inferiores en un conjunto de resultados hay dos maneras. La primera es ordenar el resultado en orden descendente en lugar de ascendente. Esto coloca los resultados inferiores en la parte superior. A continuación, puede utilizar fetch como de costumbre.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Si no desea alterar el orden de los resultados, puede usar una consulta secundaria para obtener el recuento de registros. Esto se puede usar para ajustar el desplazamiento en consecuencia.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY

Hay un cierto peligro en este método, ya que la expresión para calcular el DESPLAZAMIENTO puede resultar en un valor menor que cero. En nuestro ejemplo, esto podría suceder si el número total de filas fuera inferior a diez. Para defenderse de esta condición, desea incorporar una instrucción de caso en su lógica para verificar esta condición.

Datos de muestreo en el medio de un conjunto de resultados

Una buena característica de FETCH and OFFSET es que puede escribir SQL para muestrear o probar datos desde el medio del resultado. Esto es muy útil si necesita ver echar un vistazo a la mitad de un conjunto de resultados, tal vez uno que contenga millones de filas, sin mostrar todas las filas hasta el punto que desea revisar.

Para muestrear en el medio, usaría la misma lógica que para la paginación. Por supuesto, el número de filas que obtenga en este caso puede ser mucho mayor.

El efecto de ORDER BY on OFFSET y FETCH

OFFSET y FETCH solo funcionan junto con una cláusula ORDER BY. En efecto, SQL primero recupera los datos especificados, como columnas, y luego ordena los datos en orden ascendente o descendente.

Solo después de completar este paso se omiten las filas y se producen los resultados.

Casos límite

Dado que algunos valores pueden dar lugar a un error, como un desplazamiento negativo, exploremos varias combinaciones de valores que pueden exceder el número de filas en una tabla para comprender qué valores son seguros de usar y qué valores arrojarían un error SQL.

Basaremos nuestros ejemplos en los recursos humanos.Tabla de empleados, que contiene 290 filas.

El único caso que se traduce en un error es cuando el DESPLAZAMIENTO es negativo. Hay casos que no devuelven filas, o pueden devolver menos filas de las que cree, pero esos casos no arrojan errores. En su mayor parte, esas situaciones ocurren cuando el valor de DESPLAZAMIENTO es mayor que el número de filas en el resultado (todas las filas se omiten), o está tratando de obtener «pasado» y luego el final del resultado.