¡Hola, chicos!
En este artículo te demostraré cuándo debes usar ORDER BY y cuándo no debes usarlo en absoluto, porque no tiene ningún efecto en la práctica y solo hace que nuestra consulta demore más y consuma más recursos.

El objetivo principal de este artículo es romper el mito de que los datos se ordenan físicamente en la tabla cuando se hace INSERT... FROM SELECT y ORDER BY, lo que hace que muchos programadores insistan en usar ORDER BY en las operaciones INSERT, un escenario que encuentro mucho entre los clientes de consultoría y es mucho más común de lo que me gustaría.

Para empezar, voy a hacer una prueba muy sencilla con esto, creando una nueva tabla e insertando los datos en la tabla desde una consulta en sys.objects:

CREATE TABLE dbo.Post_OrderBy (
    Id INT IDENTITY(1,1) NOT NULL,
    Nome VARCHAR(900) NOT NULL
)

INSERT INTO dbo.Post_OrderBy
SELECT [name]
FROM sys.objects
ORDER BY name

SELECT * FROM dbo.Post_OrderBy

Y cuando analizamos los resultados, vemos que incluso usando ORDER BY en INSERT, los registros no se están ordenando:

Bueno, el mito ha sido desacreditado. Esta teoría de que vale la pena ordenar datos en INSERT para no necesitar usar ORDER BY en SELECT se ha derrumbado. En otras palabras, en este tipo de escenario, NUNCA debes usar ORDER BY para insertar datos en una tabla, a menos que tengas una cláusula TOP en SELECT.

Ahora explicaré por qué sucede esto. Como ya expliqué en mi artículo. Comprender cómo funcionan los índices en SQL Server, el índice se encarga de ordenar estos registros, ya que los índices generalmente utilizan algoritmos como QuickSort, que requieren que los datos estén ordenados para obtener el máximo rendimiento en las operaciones de Búsqueda.

Para demostrar cómo el índice ordena los datos, crearé un índice agrupado y uno no agrupado en la tabla y luego consultaré los datos:

CREATE CLUSTERED INDEX SK01_Post_OrderBy ON dbo.Post_OrderBy(Id) WITH(FILLFACTOR=100)
CREATE NONCLUSTERED INDEX SK02_Post_OrderBy ON dbo.Post_OrderBy(Nome) WITH(FILLFACTOR=100)

Resultado de la consulta:

¿Viste cómo se ordenaron los datos? Genial, verdad 🙂

Entonces, ¿podemos confiar en que los índices mantendrán los datos de mi tabla siempre ordenados y ya no necesito usar ORDER BY ni siquiera en consultas? ¡¡NO!! Los índices están sujetos a fragmentación de datos a medida que se insertan/eliminan/actualizan, perdiendo el orden en las páginas de índice.

En el ejemplo del índice que creé arriba, especifiqué el FILLFACTOR del índice como 100%, es decir, el índice ordenará todos los datos durante la creación y los almacenará de manera ordenada, tratando de ocupar todas las páginas del índice (cada página contiene 8 KB, entre datos y encabezados), sin dejar ningún espacio vacío para cualquier dato nuevo que se inserte/actualice en la tabla. En otras palabras, como SQL Server no tiene espacio libre en el índice, si inserto un nuevo registro en esta tabla, ese registro no se insertará en orden sino al final de la última página (o se creará una nueva página para él, si ya están todas llenas).

Después de crear mi índice, donde ordenó los registros, la fragmentación de mis índices debería ser muy cercana a cero:

SELECT
    OBJECT_NAME(B.object_id) AS TableName,
    B.name AS IndexName,
    A.index_type_desc AS IndexType,
    A.avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')	A
    INNER JOIN sys.indexes							B	WITH(NOLOCK) ON B.object_id = A.object_id AND B.index_id = A.index_id
WHERE
    OBJECT_NAME(B.object_id) = 'Post_OrderBy'
ORDER BY
    A.avg_fragmentation_in_percent DESC

Resultado:

Y ahora fuercemos la fragmentación de datos usando el siguiente comando T-SQL:

-- Força a fragmentação do índice
DECLARE @Contador INT = 1, @Total INT = (SELECT COUNT(*) FROM dbo.Post_OrderBy)

WHILE(@Contador <= @Total)
BEGIN
    
    UPDATE dbo.Post_OrderBy
    SET Nome = REPLICATE(CHAR(65 + (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 57), (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * 10)
    WHERE Id = @Contador

    INSERT INTO dbo.Post_OrderBy
    SELECT [name]
    FROM sys.objects
    WHERE SUBSTRING([name], 1, 1) NOT IN ('D', 'H', 'M', 'T', 'S')


    SET @Contador += 1

END

Analizando el nivel de fragmentación del índice:

¿Mis datos siguieron ordenados con este nivel de fragmentación?

Vixi... Ahora todo está en mal estado... Para corregir este problema de fragmentación causado por el script que usé anteriormente, podemos usar las operaciones REORGANIZE (Recomendación: 5 a 30% de fragmentación) y REBUILD (Recomendación: Más del 30% de fragmentación) para reordenar los datos en el índice:

ALTER INDEX ALL ON dbo.Post_OrderBy REBUILD

O RECONSTRUIR/REORGANIZAR también se puede hacer en índices individuales:

ALTER INDEX SK01_Post_OrderBy ON dbo.Post_OrderBy REBUILD
ALTER INDEX SK02_Post_OrderBy ON dbo.Post_OrderBy REBUILD

Como resultado, nuestra fragmentación disminuyó:

Y los datos se vuelven a ordenar. Pero, ¿podemos confiar únicamente en el ordenamiento por índices para devolver datos ordenados? La respuesta es: ¡NO! EL ÚNICO Una forma confiable de devolver datos de forma ordenada es mediante ORDER BY.

Pero aquí viene la pregunta clave: ¿realmente necesitas ordenar estos datos en la base de datos? En muchos escenarios (por no decir la mayoría), la respuesta es NO. Los datos se pueden consultar perfectamente en la base de datos sin clasificarlos, devolverlos a la aplicación y clasificarlos allí, en la interfaz de usuario. El problema con este enfoque es que "le da más trabajo" al desarrollador y, por lo tanto, rara vez vemos que este escenario suceda a diario, lo cual es una pena, ya que es el escenario ideal, ya que no cambia nada para el usuario final y el banco no está cargado con varias operaciones de clasificación en consultas "pesadas".

¿Existen escenarios en los que ORDER BY en la consulta de la base de datos sea justificable? ¡Sí definitivamente! Especialmente en escenarios en los que el operador TOP devuelve los registros más grandes/pequeños del TOP N según algunos criterios de clasificación. En este caso, se recomienda ORDER BY, ya que es mejor ordenar una tabla de 10 millones de registros en la base de datos y devolver los 10 más grandes, que devolver los 10 millones de registros y filtrarlos/ordenarlos en la aplicación.

Bueno chicos, espero que hayan disfrutado este artículo y hayan podido aclarar algunas dudas sobre índices, fragmentación, factor de relleno (crearé una publicación más detallada sobre esto más adelante) y cuándo se debe usar ORDER BY y cuándo NUNCA se debe usar.

Si es desarrollador, le recomiendo encarecidamente que considere eliminar ORDER BY de las consultas y comenzar a ordenar estos registros en la capa de aplicación en lugar de en la base de datos. El DBA y la base de datos gracias 🙂

¿Te gusta estudiar Performance Tuning? Así que no pierdas el tiempo y empieza a leer mis artículos de la Serie. Ajuste de rendimiento y espero que termines estas lecturas con otra vista de tu base de datos.

¡Un abrazo enorme para ti y nos vemos en el próximo post!