SQL Server diferencias entre TOP y OFFSET FETCH

TOP y la dupla OFFSET – FETCH permiten limitar el número de registros devueltos.

TOP.

Devuelve un número específico de registros de una consulta.

Ha estado disponible desde la versión de SQL Server 2000.

La versión 2000 requería escribir directamente el entero, no se podía usar una variable.

SELECT TOP 10 a FROM tabla

Tampoco se puede usar paréntesis:

SELECT TOP (10) a from tabla

Desde la versión 2005 se puede usar una variable, una expresión o una sentencia.

SELECT TOP (@variable) a FROM tabla ORDER BY a
SELECT TOP (SELECT COUNT(*) FROM otraTabla) a FROM tabla ORDER BY a
SELECT TOP (@variable + 5 * 4 / 2) a FROM tabla ORDER BY a

TOP no cumple con el estándar ANSI y está limitado a productos de Microsoft tales como SQL Server y Access.

La cláusula TOP puede usarse con ORDER BY o sin ORDER BY.

ORDER BY no es requerido pero si no ordenas no hay garantías sobre que registros devolverá.

TOP soporta el uso de porcentajes.

SELECT TOP 10 PERCENT a FROM tabla

El número de registros devuelto se redondea hacia arriba. Ejemplo: 50.4 -> 51.

Para obtener los últimos registros puedes hacer lo siguiente:

SELECT TOP 10 a FROM tabla ORDER BY a DESC

Como está ordenado descendentemente los resultados están al revés.

Soporta WITH TIES que permite mostrar registros adicionales que tienen el mismo valor. Tener en cuenta que su rendimiento se verá afectado si no encuentra un buen índice.

Un poco de historia. Antes de SQL Server 2000 no había TOP se tenía que usar SET ROWCOUNT número entero o variable.

SET ROWCOUNT @max
SELECT * FROM tabla
SET ROWCOUNT 0

No olvidar la última línea para quitar el límite de n registros impuesto por el primer SET ROWCOUNT.

OFFSET y FETCH.

Devuelven una “ventana de registros” de los resultados. OFFSET especifica cuantos registros tiene que saltarse dentro del resultado y FETCH especifica cuantos registros desde ese punto en adelante tiene que devolver.

Fueron introducidos desde SQL Server 2012 y cumplen con ANSI.

Puedes usar OFFSET sin FETCH pero FETCH no se puede usar solo.

Se deben usar con una cláusula ORDER BY. Y la razón es muy simple: OFFSET y FETCH son parte de la cláusula ORDER BY.

SELECT a FROM tabla
ORDER BY a
 OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY

El siguiente ejemplo obtiene los mismos resultados que un TOP 10.

SELECT a FROM tabla
ORDER BY a
 OFFSET 0 ROWS
 FETCH NEXT 10 ROWS ONLY

Cuando OFFSET es igual a 0, ningún registro es saltado.

No tienen una forma innata para devolver un porcentaje de registros. Sin embargo puedes calcularlo tú mismo así:

SELECT a FROM tabla
ORDER BY a
 OFFSET 0 ROWS
 FETCH NEXT (SELECT CAST(CEILING(COUNT(*) * .1) AS INT) FROM tabla) ROWS ONLY

El truco es usar un subquery (sub-consulta).

Para obtener los últimos registros puedes invertir el orden como lo hicimos para el TOP.

SELECT a FROM tabla
ORDER BY a DESC
 OFFSET 0 ROWS
 FETCH NEXT 10 ROWS ONLY

Otra forma es usar una sub-consulta.

SELECT a
FROM tabla
ORDER BY a
 OFFSET (SELECT COUNT(*) FROM a)-10 ROWS
 FETCH NEXT 10 ROWS ONLY

Este método es peligroso ya que el cálculo de OFFSET podría resultar en un valor menor que 0. En el ejemplo si el número de registros es menor que 10. Para evitar esta condición tendrías que usar una sentencia CASE.

Si el OFFSET es superior al número de registros, no devuelve registros.

Paginación.

Uno de los usos populares de OFFSET y FETCH es la paginación.

Suponiendo que tenemos una página web que muestra empleados ordenados por FechaContratación. Si queremos mostrar 20 empleados por página y estamos mostrando la tercera página (empleados del 21 – 30) podemos usar lo siguiente:

SELECT ID, Titulo, FechaContratacion 
FROM Empleados
ORDER BY FechaContratacion
 OFFSET 20 ROWS
 FETCH NEXT 10 ROWS ONLY

Comparación.

Item TOP OFFSET y FETCH
ANSI No
Versión de SQL Server 2000 2012
Requiere ORDER BY No
Porcentajes No (usar subqueries)
Limita número de registros devueltos No
Devuelve últimos registros Ordena Descendente
Bueno para paginación No
Devuelve resultados del “medio” No
WITH TIES No

Enlace al documento original en inglés en CodeProject.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s