El ordenamiento
de datos se supone que es una cosa trivial, pero resulta, que es el origen de
mucha confusión en T-SQL. Esta post describe la diferencia crítica en T-SQL
entre los datos no ordenados y los ordenados. Luego se describen las
herramientas que T-SQL proporciona para ordenar los datos.
Entender
Cuando el Orden está Garantizado
Probablemente
uno de los aspectos más confusos de trabajar con T-SQL es comprender cuando un
resultado de consulta está garantizado que será retornado en un orden en
particular versus cuando no lo es. El correcto entendimiento de este aspecto del
lenguaje se vincula directamente a los fundamentos de T-SQL, particularmente la
teoría de conjuntos matemática. Si entiende esto desde las primeras etapas de
la escritura de código T-SQL, tendrá épocas mucho más fáciles, de la que muchos
otros, simplemente tienen suposiciones y expectativas incorrectas del lenguaje.
Considere la siguiente consulta como ejemplo.
SELECT
empid, firstname,
lastname, city,
MONTH(birthdate) AS birthmonth
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA';
¿Hay una garantía de que las filas serán retornadas en un
orden en particular, y si es así, ¿cuál es ese orden?
Algunos hacen una suposición intuitiva de que las filas serán
retornadas en el orden de inserción; algunos asumen el orden de llave primaria;
algunos asumen el orden de índice clustered; otros conocen que no hay garantía
para cualquier tipo de orden.
Si recuerda que una tabla en T-SQL se supone que
representa una relación; una relación es un conjunto, y un conjunto no tiene un
orden de sus elementos. Con esto en mente, a menos que se le instruya explícitamente
a la consulta de otro modo, el resultado de una consulta no tiene un orden
garantizado. Por ejemplo, esta consulta dio el siguiente resultado cuando se
ejecuta en un sistema.
empid firstname lastname city birthmonth
------ ---------- ---------
--------- -----------
1 Sara Davis Seattle 12
2 Don Funk Tacoma 2
3 Judy Lew Kirkland 8
4 Yael Peled Redmond 9
8
Maria Cameron
Seattle 1
Podría parecer que la salida está ordenada por empid,
pero eso no está garantizado. Lo qué podría ser más confuso es que si ejecuta
la consulta repetidamente, parece que el resultado sigue siendo retornado en el
mismo orden; pero una vez más, no está garantizado. Cuando el motor de base de
datos (SQL Server en este caso) procesa esta consulta, se sabe que puede
retornar los datos en cualquier orden porque no hay alguna instrucción
explícita para retornar los datos en un orden específico. Podría ser que,
debido a la optimización y otras razones, el motor de base de datos SQL Server
eligió procesar los datos de una manera particular esta vez. Incluso hay cierta probabilidad de que tales opciones
serán repetidas si las circunstancias
físicas siguen siendo las mismas. Pero hay una gran diferencia entre lo que es
probable que ocurra debido a la optimización y a otras razones y lo que
realmente está garantizado.
El motor de base de datos puede, y a veces lo hace,
cambiar las opciones que pueden afectar el orden en el cual las filas son
retornadas, sabiendo que es libre de hacerlo. Ejemplos para tales cambios en
las opciones, incluyen cambios en la distribución de datos, la disponibilidad
de las estructuras físicas, tales como índices, y la disponibilidad de recursos
como los CPUs y la memoria. Además, con los cambios en el motor después de
actualizar a una nueva versión del producto, o incluso después de la aplicación
de un paquete de servicio, los aspectos de optimización pueden cambiar. A su
vez, estos cambios podrían afectar, entre otras cosas, el orden de las filas en
el resultado.
En
pocas palabras, esto no puede ser enfatizado lo suficiente: Una consulta que no
tiene una instrucción explícita para retornar las filas en un orden particular,
no garantiza el orden de las filas en el resultado. Cuando necesite tal
garantía, la única manera de proporcionarla es añadiendo una cláusula ORDER BY a
la consulta.
Usando
la Cláusula ORDER BY para Ordenar los Datos
La única manera de garantizar realmente que las filas son
retornadas de una consulta en un cierto orden es, agregando la cláusula ORDER
BY.
Por ejemplo, si quiere retornar información sobre los
empleados del estado de Washington en los Estados Unidos, ordenados por ciudad,
se especifica la columna ciudad en la cláusula ORDER BY de la siguiente manera.
SELECT
empid, firstname,
lastname, city,
MONTH(birthdate) AS birthmonth
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA'
ORDER BY city;
Aquí está la salida de esta consulta.
empid firstname lastname city birthmonth
------ ---------- ---------
--------- -----------
3 Judy Lew Kirkland
8
4 Yael Peled Redmond
9
8 Maria Cameron
Seattle 1
1 Sara Davis Seattle
12
2
Don Funk
Tacoma 2
Si no indica una dirección para ordenamiento, el orden
ascendente es asumido por defecto. Puede ser explícito y especificar city ASC,
pero significa lo mismo que no indicar la dirección. Para el ordenamiento descendente,
es necesario especificar explícitamente DESC, como sigue.
SELECT
empid, firstname,
lastname, city,
MONTH(birthdate) AS birthmonth
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA'
ORDER BY city DESC;
Esta vez, la salida muestra las filas en orden de ciudad,
en sentido descendente.
empid firstname lastname city birthmonth
------ ---------- ---------
--------- -----------
2 Don Funk Tacoma 2
1 Sara Davis Seattle 12
8 Maria
Cameron Seattle 1
4 Yael Peled Redmond 9
3 Judy Lew Kirkland 8
La columna city no es única en el país y la región filtrada,
y por lo tanto, la ordenación de las filas con la misma ciudad (ver Seattle,
por ejemplo) no está garantizada. En tal caso, se dice que el orden no es
determinístico. Al igual que una consulta sin una cláusula ORDER BY no
garantiza el orden entre las filas de resultados en general, una consulta con
ORDER BY city, cuando la ciudad no es única, no garantiza el orden entre las
filas con la misma ciudad. Afortunadamente, puede especificar varias
expresiones en la lista ORDER BY, separados por comas. Un caso de uso de esta
capacidad es aplicar un desempate para el ordenamiento. Por ejemplo, se podría
definir empid como la columna de ordenación secundaria, de la siguiente manera.
SELECT
empid, firstname,
lastname, city,
MONTH(birthdate) AS birthmonth
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA'
ORDER BY city, empid;
Aquí está la salida de esta consulta.
empid firstname lastname city birthmonth
------ ---------- ---------
--------- -----------
3 Judy Lew Kirkland
8
4 Yael Peled Redmond 9
1 Sara Davis Seattle 12
8 Maria Cameron
Seattle 1
2
Don Funk Tacoma 2
La lista ORDER BY es ahora única; por lo tanto, el orden
es determinístico. Mientras los datos originarios no cambien, los resultados
están garantizados a ser repetibles, además de su orden de presentación. Puede
indicar la dirección de ordenamiento sobre una base de expresión por expresión,
como en ORDER BY col1 DESC, col2, col3 DESC (col1 descendente, luego col2
ascendente, luego col3 descendente).
Con T-SQL, se puede ordenar por posiciones ordinales de
las columnas en la lista SELECT, pero es considerada una mala práctica. La
siguiente consulta muestra un ejemplo.
SELECT
empid, firstname,
lastname, city,
MONTH(birthdate) AS birthmonth
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA'
ORDER BY 4, 1;
En esta consulta, está pidiendo ordenar las filas por la
cuarta expresión en la lista SELECT (city) y, luego, por la primera (empid). En
esta consulta en particular, es equivalente a utilizar ORDER BY city, empid.
Sin embargo, esta práctica es considerada mala por un número de razones. Por un
lado, T-SQL mantiene un seguimiento de las posiciones ordinales de las columnas
en una tabla, además a un resultado de consulta, pero esto no es relacional.
Recordemos que el encabezado de una relación es un conjunto de atributos, y un conjunto
no tiene un orden. Además, cuando está utilizando las posiciones ordinales, es
muy fácil después de hacer cambios a la lista SELECT, omitir los cambios de los
ordinales correspondientes. Por ejemplo, suponga que decide aplicar cambios a
la consulta anterior, retornando la ciudad justo después de empid en la lista
SELECT. Aplica el cambio a la lista SELECT, pero se olvida de cambiar la lista
ORDER BY en consecuencia, y termina con la siguiente consulta.
SELECT
empid, city,
firstname, lastname,
MONTH(birthdate) AS birthmonth
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA'
ORDER BY 4, 1;
Ahora la consulta esta ordenando los datos por apellido y
por empid en lugar de ciudad y de empid. En resumen, es una buena práctica
referirse a los nombres de columna o expresiones basadas en estas, y no a las
posiciones ordinales.
Note que puede ordenar las filas de resultado por
elementos que no está retornando. Por ejemplo, la siguiente consulta retorna, para
cada empleado, la empid y la ciudad, ordenando las filas de resultado por la
fecha de nacimiento del empleado.
SELECT
empid, city
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA'
ORDER BY birthdate;
Aquí está la salida de esta consulta.
empid city
----------- ---------------
4 Redmond
1 Seattle
2 Tacoma
8 Seattle
3
Kirkland
Por supuesto, el resultado podría parecer mucho más
significativo si se incluye el atributo birthdate, pero tiene sentido no
incluirlo, es perfectamente válido. La regla es, puede ordenar las filas de
resultado por elementos que no son parte de la lista SELECT, siempre que en las
filas de resultado, puedan normalmente ser permitidas. Esta regla cambia cuando
la cláusula DISTINCT es también especificada, y por una buena razón. Cuando
DISTINCT es utilizada, los duplicados son eliminados; entonces, las filas de
resultado no necesariamente se asignan a las filas de origen en una manera
uno-a-uno, en lugar de uno-a-muchos. Por ejemplo, trate de razonar por qué la
siguiente consulta no es válida.
SELECT
DISTINCT city
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA'
ORDER BY birthdate;
Puede tener varios empleados, cada uno con una diferente
fecha de nacimiento, de la misma ciudad. Pero está retornando solo una fila por
cada ciudad distinta en el resultado. Así que dada una ciudad (por ejemplo,
Seattle) con varios empleados, ¿Cuáles fechas de nacimiento de los empleados debería
aplicarse como el valor de ordenamiento? La consulta no elegirá uno; en su
lugar, simplemente fallará.
Así, en caso de que la cláusula DISTINCT sea utilizada,
está limitada en la lista ORDER BY a sólo los elementos que aparecen en la
lista SELECT, como en la siguiente consulta.
SELECT
DISTINCT city
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA'
ORDER BY city;
Ahora la consulta es perfectamente razonable, retornando
la siguiente salida.
city
---------
Kirkland
Redmond
Seattle
Tacoma
Lo que es también interesante notar acerca de la cláusula
ORDER BY es que se evalúa conceptualmente después de la cláusula SELECT, a
diferencia de la mayoría de las otras cláusulas de consulta. Esto significa que
los alias de columnas asignadas en la cláusula SELECT son actualmente visibles
a la cláusula ORDER BY. Como un ejemplo, la siguiente consulta utiliza la
función MONTH para retornar el mes de nacimiento, asignando la expresión con el
alias de columna birthmonth. La consulta entonces se refiere al alias de
columna birthmonth directamente en la cláusula ORDER BY.
SELECT
empid, firstname,
lastname, city,
MONTH(birthdate) AS birthmonth
FROM
HR.Employees
WHERE
country = N'USA'
AND region = N'WA'
ORDER BY birthmonth;
Esta consulta retorna la siguiente salida.
empid firstname lastname city birthmonth
------ ---------- ---------
--------- -----------
8
Maria Cameron Seattle 1
2
Don Funk
Tacoma 2
3 Judy Lew
Kirkland 8
4 Yael
Peled Redmond 9
1
Sara Davis Seattle 12
Otro aspecto difícil del ordenamiento es el tratamiento
de NULLs. Recordemos que un NULL representa un valor faltante, así cuando
compara un NULL a algo, se obtiene el resultado lógico Desconocido. Ese es el
caso incluso cuando compara dos NULLs. Así que no es tan trivial preguntar cómo
deberían comportarse los NULLs en términos de ordenación. ¿Deberían todos estar
ordenados juntos? Si es así, debería ordenarse antes o después de los valores no
NULLs? El SQL estándar dice que los NULLs deberían ordenarse juntos, pero dejarle
a la implementación a decidir si los ordena antes o después de los valores no
NULL. En SQL Server la decisión fue ordenarlos antes de los no NULLs (cuando se
utiliza una dirección ascendente). A modo de ejemplo, la siguiente consulta retorna
para cada pedido la orderid y la fecha de envío, ordenada por el último.
SELECT
orderid, shippeddate
FROM
Sales.Orders
WHERE
custid = 20
ORDER
BY shippeddate;
Recuerde que los pedidos no enviados tienen un NULL en la
columna shippeddate; por lo tanto, se ordenan antes de los pedidos enviados,
como muestra la salida de la consulta.
orderid
shippeddate
-----------
-----------------------
11008
NULL
11072
NULL
10258
2006-07-23
00:00:00.000
10263
2006-07-31 00:00:00.000
10351
2006-11-20 00:00:00.000
...
El SQL estándar soporta las opciones NULLS FIRST y NULLS
LAST para controlar cómo ordenar los NULLs, pero T-SQL no soporta esta opción.
Como un reto interesante, ver si se puede encontrar como ordenar los pedidos
por fecha de envío ascendente, pero que tengan los NULLs ordenados al último.
(Sugerencia: Puede especificar expresiones en la cláusula ORDER BY; pensar en
cómo utilizar la expresión CASE para lograr esta tarea).
Así que recuerde, una consulta sin una cláusula ORDER BY
retorna un resultado relacional (al menos desde una perspectiva de
ordenamiento), y por lo tanto no garantiza algún orden. La única manera de
garantizar el orden es con una cláusula ORDER BY. De acuerdo al SQL estándar,
una consulta con una cláusula ORDER BY conceptualmente retorna un cursor y no una relación.
La indexación es discutida más adelante, pero por ahora,
basta decir que la creación de índices correctos puede ayudar a SQL Server a
evitar la necesidad de ordenar realmente los datos, por dirigir una petición ORDER
BY. Sin buenos índices, SQL Server necesita ordenar los datos, y el ordenamiento
puede ser costoso, especialmente cuando un conjunto extenso está implicado. Si
no necesita retornar los datos ordenados, asegúrese de no especificar una
cláusula ORDER BY, para evitar costos innecesarios.
Ejercicio
1: Use la Cláusula ORDER BY con Ordenamiento no Determinístico
En este ejercicio,
practica el uso de la cláusula ORDER BY para ordenar los datos, practicando el
ordenamiento no determinístico.
1. Abra el SSMS y conéctese a la base de
datos de muestra TSQL2012.
2. Se le pedirá que escriba una consulta
que retorne los pedidos para el cliente 77. Utilice la siguiente consulta.
SELECT orderid, empid, shipperid, shippeddate
FROM Sales.Orders
WHERE custid =
77;
Aparecerá el siguiente conjunto de
resultados.
orderid empid shipperid
shippeddate
-------- ------
---------- -----------------------
10310 8 2
2006-09-27 00:00:00.000
10708 6 2
2007-11-05 00:00:00.000
10805 2 3
2008-01-09 00:00:00.000
10992 1 3
2008-04-03 00:00:00.000
Note que debido a que no se especifica
una cláusula ORDER BY, no hay garantía de que las filas serán retornadas en el
orden mostrado en el código anterior. La única garantía que tiene es que
obtendrá este conjunto particular de filas.
3. Se le pide que revise su consulta de
manera que las filas serán ordenadas por shipperid. Añadir una cláusula ORDER
BY, de la siguiente manera.
SELECT
orderid, empid,
shipperid, shippeddate
FROM
Sales.Orders
WHERE custid = 77
ORDER BY
shipperid;
La consulta ahora retorna el siguiente
resultado.
orderid empid shipperid
shippeddate
-------- ------
---------- -----------------------
10310 8 2
2006-09-27 00:00:00.000
10708 6 2
2007-11-05 00:00:00.000
10805 2 3
2008-01-09 00:00:00.000
10992 1 3
2008-04-03 00:00:00.000
Ahora garantiza que las filas serán
retornadas ordenadas por shipperid, pero ¿es el ordenamiento determinístico?
Por ejemplo, puede saber con certeza cuál será el orden entre las filas con el
mismo shipperid? La respuesta es no.
Ejercicio
2: Utilice la Cláusula ORDER BY con Ordenamiento Determinístico
En
este ejercicio, practica el uso de la cláusula ORDER BY para ordenar los datos,
practicando el ordenamiento determinístico.
1. Comienza este paso con la consulta que
escribió en el paso 3 del ejercicio 1. Se le da un requerimiento para agregar
un ordenamiento secundario por fecha de envío, descendente. Añadir shippeddate
DESC a la cláusula ORDER BY, de la siguiente manera.
SELECT orderid, empid, shipperid, shippeddate
FROM Sales.Orders
WHERE custid = 77
ORDER BY shipperid,
shippeddate DESC;
La consulta ahora retorna el siguiente
resultado.
orderid
empid shipperid shippeddate
--------
------ ---------- -----------------------
10708 6 2
2007-11-05 00:00:00.000
10310 8 2
2006-09-27 00:00:00.000
10992 1 3
2008-04-03 00:00:00.000
10805 2 3 2008-01-09
00:00:00.000
A diferencia del paso 3, ahora está
garantizado que las filas con el mismo shipperid, serán ordenadas por fecha de
envío, descendente. ¿Es el ordenamiento ahora determinístico? ¿Puede decir con
certeza cuál será el orden entre las filas con el mismo shipperid y fecha de
envío? La respuesta sigue siendo no, ya que la combinación de las columnas shipperid
y shippeddate no es única, no importa lo que los valores actuales que ve en la
tabla, pueden llevarle a pensar. Técnicamente, podría haber varias filas en el
resultado de esta consulta con los mismos valores shipperid y shippeddate.
2. Se le pedirá que revise la consulta
del paso 1 garantizando el ordenamiento determinístico. Necesita definir un
desempate. Por ejemplo, defina orderid DESC como un desempate, como sigue.
SELECT orderid, empid, shipperid, shippeddate
FROM Sales.Orders
WHERE custid = 77
ORDER BY shipperid,
shippeddate DESC,
orderid DESC;
Ahora, en caso de empate en los
valores shipperid y shippeddate, la fila con el valor orderid más grande será
ordenado primero.
Este post muestra el ordenamiento de datos. Espero les sea de utilidad.
ResponderBorrar