Los filtros clásicos en SQL como ON, WHERE y HAVING se basan en predicados. TOP y OFFSET-FETCH son filtros que se basan en un concepto diferente: se indica el orden y cuántas filas filtrar en base a ese orden. Muchas tareas de filtrado se definen en base al orden y a un número de filas requerido. Ciertamente, es bueno contar con un soporte de lenguaje en T-SQL que permita redactar la solicitud de forma similar a la forma en que se piensa en la tarea.
Este capítulo comienza con los aspectos de diseño lógico de los filtros. A continuación, utiliza un escenario de paginación para demostrar su optimización. El capítulo también cubre el uso de TOP con declaraciones de modificación. Por último, el capítulo demuestra el uso de TOP y OFFSET-FETCH en la resolución de problemas prácticos como el top N por grupo y la mediana.
Los filtros TOP y OFFSET-FETCH
Los filtros TOP y OFFSET-FETCH se utilizan para implementar los requisitos de filtrado en las consultas de forma intuitiva. El filtro TOP es una característica propia de T-SQL, mientras que el filtro OFFSET-FETCH es una característica estándar. T-SQL comenzó a soportar OFFSET-FETCH con Microsoft SQL Server 2012. A partir de SQL Server 2014, la implementación de OFFSET-FETCH en T-SQL todavía carece de un par de elementos estándar -interesantemente, unos que están disponibles con TOP. Con la implementación actual, cada uno de los filtros tiene capacidades que no son soportadas por el otro.
Empezaré por describir los aspectos de diseño lógico de TOP y luego cubriré los de OFFSET-FETCH.
El filtro TOP
El filtro TOP es una construcción comúnmente utilizada en T-SQL. Su popularidad puede atribuirse probablemente al hecho de que su diseño está tan bien alineado con la forma en que se expresan muchos requisitos de filtrado-por ejemplo, «Devolver los tres pedidos más recientes.» En esta petición, el orden para el filtro se basa en orderdate, descendente, y el número de filas que se quiere filtrar en base a este orden es 3.
Se especifica la opción TOP en la lista SELECT con un valor de entrada escrito como BIGINT que indica cuántas filas se quieren filtrar. La especificación de la ordenación se realiza en la cláusula clásica ORDER BY. Por ejemplo, utilice la siguiente consulta para obtener los tres pedidos más recientes.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Obtuve la siguiente salida de esta consulta:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
En lugar de especificar el número de filas que desea filtrar, puede utilizar TOP para especificar el porcentaje (del número total de filas en el resultado de la consulta). Para ello, proporcione un valor en el rango de 0 a 100 (escrito como FLOAT) y añada la palabra clave PERCENT. Por ejemplo, en la siguiente consulta se solicita filtrar el uno por ciento de las filas:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server redondea el número de filas calculado en función del porcentaje introducido. Por ejemplo, el resultado del 1 por ciento aplicado a 830 filas de la tabla Pedidos es 8,3. Redondeando este número, se obtiene 9. Esta es la salida que obtuve para esta consulta:
orderid orderdate custid empid----------- ---------- ----------- -----------11074 2015-05-06 73 711075 2015-05-06 68 811076 2015-05-06 9 411077 2015-05-06 65 111070 2015-05-05 44 211071 2015-05-05 46 111072 2015-05-05 20 411073 2015-05-05 58 211067 2015-05-04 17 1
Tenga en cuenta que para traducir el porcentaje de entrada a un número de filas, SQL Server tiene que calcular primero el recuento de filas en el resultado de la consulta, y esto suele requerir trabajo adicional.
Es interesante que la especificación del orden sea opcional para el filtro TOP. Por ejemplo, considere la siguiente consulta:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Obtuve la siguiente salida de esta consulta:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
La selección de las tres filas a devolver es no determinista. Esto significa que si ejecuta la consulta de nuevo, sin que los datos subyacentes cambien, teóricamente podría obtener un conjunto diferente de tres filas. En la práctica, la selección de filas dependerá de condiciones físicas como las opciones de optimización, las opciones del motor de almacenamiento, la disposición de los datos y otros factores. Si se ejecuta la consulta varias veces, mientras esas condiciones físicas no cambien, es probable que se sigan obteniendo los mismos resultados. Pero es fundamental entender el principio de «independencia física de los datos» del modelo relacional, y recordar que en el nivel lógico no se garantiza la repetibilidad de los resultados. Si no se especifica el orden, se debe considerar que el orden es arbitrario, lo que resulta en una selección de filas no determinista.
Incluso cuando se proporciona la especificación del orden, no significa que la consulta sea determinista. Por ejemplo, una consulta TOP anterior utilizó orderdate, DESC como especificación de orden. La columna fecha de pedido no es única; por lo tanto, la selección entre filas con la misma fecha de pedido no es determinista. Entonces, ¿qué hacer en los casos en los que se debe garantizar el determinismo? Hay dos opciones: utilizar WITH TIES o el ordenamiento único.
La opción WITH TIES hace que los vínculos se incluyan en el resultado. Así es como se aplica a nuestro ejemplo:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Este es el resultado que obtuve de esta consulta:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 811074 2015-05-06 73 7
SQL Server filtra las tres filas con las fechas de pedido más recientes, además de incluir todas las demás filas que tienen la misma fecha de pedido que en la última fila. Como resultado, puede obtener más filas que el número especificado. En esta consulta, usted especificó que quería filtrar tres filas pero terminó obteniendo cuatro. Lo que es interesante observar aquí es que la selección de filas es ahora determinista, pero el orden de presentación entre las filas con la misma fecha de pedido es no determinista.
El segundo método para garantizar un resultado determinista es hacer que la especificación de ordenamiento sea única añadiendo un desempate. Por ejemplo, podría añadir orderid, DESC como desempate en nuestro ejemplo. Esto significa que, en caso de empate en los valores de la fecha de pedido, se prefiere una fila con un valor de ID de pedido superior a una fila con uno inferior. Esta es nuestra consulta con el desempate aplicado:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Esta consulta genera la siguiente salida:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
El uso del ordenamiento único hace que tanto la selección de filas como el ordenamiento de la presentación sean deterministas. El conjunto de resultados así como el orden de presentación de las filas están garantizados para ser repetibles mientras los datos subyacentes no cambien.
Si tiene un caso en el que necesita filtrar un cierto número de filas pero realmente no le importa el orden, podría ser una buena idea especificar ORDER BY (SELECT NULL), así:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
De esta manera, deja que todos sepan que su elección de orden arbitrario es intencional, lo que ayuda a evitar confusiones y dudas.
Como recordatorio de lo que expliqué en el capítulo 1, «Procesamiento lógico de consultas», sobre los filtros TOP y OFFSET-FETCH, el orden de presentación se garantiza sólo si la consulta externa tiene una cláusula ORDER BY. Por ejemplo, en la siguiente presentación de consulta, el orden no está garantizado:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
Para proporcionar una garantía de orden de presentación, debe especificar una cláusula ORDER BY en la consulta externa, así:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS DORDER BY orderdate DESC, orderid DESC;
El filtro OFFSET-FETCH
El filtro OFFSET-FETCH es una característica estándar diseñada de forma similar a TOP pero con un elemento extra. Puede especificar cuántas filas desea omitir antes de especificar cuántas filas desea filtrar.
Como podría haber adivinado, esta característica puede ser útil para implementar soluciones de paginación, es decir, devolver un resultado al usuario un trozo a la vez a petición cuando el conjunto de resultados completo es demasiado largo para caber en una pantalla o página web.
El filtro OFFSET-FETCH requiere que exista una cláusula ORDER BY, y se especifica justo después de ella. Se empieza indicando cuántas filas hay que omitir en una cláusula OFFSET, seguido de cuántas filas hay que filtrar en una cláusula FETCH. Por ejemplo, basándose en el orden indicado, la siguiente consulta omite las primeras 50 filas y filtra las siguientes 25 filas:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
En otras palabras, la consulta filtra las filas 51 a 75. En términos de paginación, suponiendo un tamaño de página de 25 filas, esta consulta devuelve la tercera página.
Para permitir un lenguaje declarativo natural, puede utilizar la palabra clave FIRST en lugar de NEXT si lo desea, aunque el significado es el mismo. Usar FIRST podría ser más intuitivo si no se salta ninguna fila. Incluso si no quiere omitir ninguna fila, T-SQL sigue obligando a especificar la cláusula OFFSET (con 0 FILAS) para evitar la ambigüedad de análisis. Del mismo modo, en lugar de utilizar la forma plural de la palabra clave ROWS, puede utilizar la forma singular ROW tanto en la cláusula OFFSET como en la cláusula FETCH. Esto es más natural si necesita omitir o filtrar sólo una fila.
Si tiene curiosidad por saber cuál es el propósito de la palabra clave ONLY, significa no incluir vínculos. El SQL estándar define la alternativa WITH TIES; sin embargo, el T-SQL no la soporta todavía. Del mismo modo, el SQL estándar define la opción PERCENT, pero T-SQL tampoco la soporta todavía. Estas dos opciones que faltan están disponibles con el filtro TOP.
Como se ha mencionado, el filtro OFFSET-FETCH requiere una cláusula ORDER BY. Si desea utilizar un orden arbitrario, como TOP sin una cláusula ORDER BY, puede utilizar el truco con ORDER BY (SELECT NULL), así:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
La cláusula FETCH es opcional. Si desea omitir un cierto número de filas pero no limitar el número de filas a devolver, simplemente no indique una cláusula FETCH. Por ejemplo, la siguiente consulta omite 50 filas pero no limita el número de filas devueltas:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
En cuanto a la ordenación de la presentación, el comportamiento es el mismo que con el filtro TOP; es decir, con OFFSET-FETCH también se garantiza la ordenación de la presentación sólo si la consulta más externa tiene una cláusula ORDER BY.