Receta T-SQL No. 3-3: Diferencia Entre COALESCE e ISNULL

Índice

0. Introducción
1. Problema
2. Solución
3. Discusión de la Solución
3.1 Número de argumentos
3.2 Precedencia de tipos de datos
3.3 Argumentos NULL
4. Práctica: Código T-SQL
5. Conclusiones
6. Glosario
7. Literatura & Enlaces

0. Introducción

En las dos recetas T-SQL anteriores (3-1, y 3-2) aprendimos a manipular valores NULL con las construcciones ISNULL y COALESCE. La primera de estas nos sirve para evaluar una expresión y en caso de ser NULL reemplazar por un valor alternativo; mientras que con la segunda construcción podemos evaluar un conjunto de valores en una lista y obtener el primer valor que no sea NULL. Ahora llega la oportunidad de contrastar estas dos construcciones y desarrollar una intuición para la determinación de los escenarios apropiados de cada uno. ¡Manos a la obra!

1. Problema

Necesitamos distinguir entre COALESCE y ISNULL.

2. Solución

Podemos distinguir la siguiente lista como las principales diferencias entre estas dos construcciones [1, 3]:

• Número de argumentos: ISNULL acepta únicamente dos argumentos, mientras que COALESCE acepta cualquier cantidad de argumentos.
• Estándar: ISNULL no es parte de la especificación ISO/ANSI SQL [2], es una construcción propietaria de Transact-SQL; por su parte, COALESCE es parte del estándar SQL.
• Conversión tipos: Con ISNULL el primer argumento toma mayor precedencia frente al segundo; mientras que COALESCE el argumento de la lista con mayor precedencia prima sobre los demás y el resultado de evaluación.
• Rendimiento: De acuerdo con [3, 4, 5], en pruebas de rendimiento la construcción COALESCE, generalmente, es más rápida que ISNULL.

3. Discusión de la Solución

Ahora pasemos a discutir con más detalle y ejemplos de demostración acerca de las diferencias principales de estas dos construcciones.

3.1 Número de argumentos

Veamos un primer ejemplo donde se demuestra el número de argumentos que soporta ISNULL y COALESCE: 

DECLARE

@x AS INT = NULL,

@y AS INT = 1759,

@z AS INT = 42;

SELECT COALESCE(@x, @y, @z);

SELECT ISNULL(@x, @y);

Notemos que COALESCE puede evaluar varias expresiones como argumento, mientras que ISNULL únicamente. Ambas expresiones retornan el valor 1759. Sin embargo, podemos crear invocaciones anidadas [1] con ISNULL; así: 

ISNULL(valor1, ISNULL(valor2, ISNULL(valor3, '')))

Pero hay que tener en cuenta que a medida que el número de argumentos aumente, sintáctimente, la expresión puede convertirse en pesada y difícil de manipular.

3.2 Precedencia tipos de datos

En la construcción COALESCE el tipo de dato de la expresión de argumentos, corresponde con el tipo del argumento de mayor precedencia (aquí sugiero consultar a [6] para saber más acerca de la precedencia de tipos de dato en T-SQL); por otro lado, el tipo de dato de una expresión ISNULL es el tipo del primer argumento.

Escribamos un ejemplo en T-SQL para su demostración: 

DECLARE

@x AS VARCHAR(5) = NULL,

@y AS VARCHAR(10) = '0987654321';

SELECT

COALESCE(@x, @y) AS COALESCE_xy, COALESCE(@y, @x) AS COALESCE_yx,

ISNULL(@x, @y) AS ISNULL_xy, ISNULL(@y, @x) AS ISNULL_yx;

Una vez ejecutemos este código en Microsoft SQL Server Management Studio obtendremos el siguiente resultado: 

Figura 1. Precedencia tipo de datos.

Notemos que en la columna COALESCE_xy el tipo del resultado es automáticamente convertido al de mayor precedencia, i.e., VARCHAR(10). Por su parte, en la columna ISNULL_xy el argumento @y ocupa la primera posición en la lista de argumentos, y en consecuencia el tipo de dato del resultado es promovido a VARCHAR(5). Esto último hace que el valor número 0987654321 se trunque: 09876.

Por otra parte, si escribimos algo como: 

SELECT COALESCE('xyz', 1);

El motor de T-SQL Server retornará el mensaje de error: 

Msg 245, ...

Conversion failed when converting the varchar value 'xyz' to data type int.

Mientras que con una expressión ISNULL como esta 

SELECT ISNULL('xyz', 1);

la ejecución del script es satisfactoria y retornará el valor 'xyz'; dado que prima el tipo de dato del primer argumento.

Pero, en [1] aclaran que cuando el primer argumento es una variable con un tipo específico pero a la que se le ha asignado NULL, la expressión 

DECLARE @varEntera INT = NULL;

SELECT ISNULL(@varEntera, 'xyz');

generá el mensaje de error 

Msg 245, ...

Conversion failed when converting the varchar value 'xyz' to data type int.

3.3 Argumentos NULL

¿Qué ocurre cuando todos los argumentos de COALESCE e ISNULL son NULL? Veámoslo con ejemplos de código T-SQL: 

SELECT COALESCE(NULL, NULL, NULL);

Esta expresión generá error. Este es el mensaje de error: 

Msg 4127, ...

At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

Por su parte, ISNULL: 

SELECT ISNULL(NULL, NULL)

genera NULL. Esto se debe a que por diseño la construcción ISNULL admite que sus dos argumentos no tengan especificado un tipo y en consecuencia se genere NULL como resultado [4].

4. Práctica: Código T-SQL

Aprovechemos esta sección práctica para resaltar el rendimiento de estas dos construcciones presentado en [7]: 

Archivo T-SQL RendimientoIsNullvsCoalesce.sql [Enlace alternativo][Enlace alternativo]: 

Después de haber ejecutado este experimento 30 veces obtenemos los siguientes resultados: 

Figura 2. Prueba rendimiento COALESCE vs ISNULL.

Notemos cómo en el experimento COALESCE, sin argumentos NULL vs. ISNULL, sin argumentos NULL el promedio de tiempo de CPU -22.63 vs. 25.23- y la desviación estándar -1.40 vs. 1.76-, la construcción COALESCE presenta mayor rendimiento que ISNULL.

Por otra parte, en el experimento COALESCE, primer argumento NULL vs. ISNULL, primer argumento NULL ISNULL presenta una diferencia poco significativa respecto al promedio de tiempo de CPU -21.93 vs. 21.47-; en cuanto a la desviación estándar -1.55 vs. 159-.

Podemos concluir, por una parte, que una prueba de rendimiento más rigurosa debería incluir más argumentos para descartar concluyentemente el rendimiento de estas dos construcciones.

[Nota importante: Este experimento fue ejecutado en un equipo donde hay varios servicios, procesos, aplicaciones ejecutándose, lo que en consecuencia introduce ruido en las mediciones; lo más apto es correr este tipo de experimento en un equipo aislado de ruidos o interrupciones internas o exteriores.]

Finalmente podemos incluir esta nota hallada en [1]: 

"There are number of discussions regarding the performance of ISNULL vs. COALESCE. For most uses of these functions, the performance differences are negligible. There are some cases when using correlated subqueries where ISNULL and COALESCE will cause the query optimizer to generate different query plans with COALESCE generating a suboptimal plan compared to ISNULL."

5. Conclusiones

Hemos estudiado con un grado de profundidad moderado las diferencias sobresalientes entre las construcciones COALESCE e ISNULL. Notamos que la precedencia de tipos, valores NULL, número de argumentos, rendimiento son los factores de comparación y de prestar atención cuando escribimos sentencias, procedimientos almacenados, o cualquier otro tipo de expresión T-SQL para evitar resultados incoherentes o incorrectos.

La próxima receta T-SQL la dedicaremos a buscar valores NULL en una tabla.

6. Glosario

• Construcción
• Expresión
• Microsoft SQL Management Studio
• Motor
• NULL
• Precedencia tipos de datos
• Rendimiento
• T-SQL
• Tipo de dato

7. Literatura & Enlaces

[1]: SQL Server 2012 T-SQL Recipes - A Problem-Solucion Approach by Jason Brimhall, David Dye, Jonathan Gennick, Andy Roberts, and Wayne Sheffield. Copyright 2012 Jason Brimhall, David Dye, Jonathan Gennick, Andy Roberts, and Wayne Sheffield, 978-1-4302-4200-0.
[2]: SQL-92 - Wikipedia, the free encyclopedia - https://en.wikipedia.org/wiki/SQL-92
[3]: ISNULL() VS. COALESCE() - SQLServerCentral - http://www.sqlservercentral.com/blogs/never_say_never/2010/02/11/isnull_2D00_vs_2D00_coalesce/
[4]: COALESCE vs. ISNULL T-SQL Functions | T-SQL content from SQL Server Pro - http://sqlmag.com/t-sql/coalesce-vs-isnull
[5]: Adam Machanic : Performance: ISNULL vs. COALESCE
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx
[6]: Data Type Precedence (Transact-SQL) - https://technet.microsoft.com/en-us/library/ms190309(v=sql.110).aspx
[7]: Adam Machanic : Performance: ISNULL vs. COALESCE - http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

V