[SQL Server] T-SQL : COALESCE vs ISNULL

Ce billet effectue quelques comparaisons élémentaires entre les fonctions COALESCE et ISNULL en T-SQL.

Présentation rapide des fonctions COALESCE et ISNULL

COALESCE sert à évaluer des arguments dans l’ordre pour ensuite retourner la valeur actuelle de la première expression qui ne prend pas la valeur NULL, tandis qu’ISNULL permet de remplacer toute valeur nulle par une expression de remplacement.

La syntaxe d’utilisation de chacune de ces fonctions est la suivante :

  • Pour COALESCE :
COALESCE ( expression [ ,...n ] )
  • Pour ISNULL :
ISNULL ( check_expression , replacement_value )

Comme on peut le voir, COALESCE prend plusieurs arguments là où ISNULL ne se contente que de deux arguments (dont le 1er correspond à une expression de vérification et le 2ème, à une expression de remplacement en cas de valeur nulle).

Notons que le mode de fonctionnement de COALESCE est équivalent à l’utilisation de plusieurs expressions CASE :

CASE
 WHEN (expression1 IS NOT NULL) THEN expression1
 ...
 WHEN (expressionN IS NOT NULL) THEN expressionN
 ELSE NULL

Différences notables

    Priorité de types de données

Comme l’indique MSDN, quand un opérateur combine deux expressions possédant un type de données différent, le type ayant la priorité la plus faible est converti dans le type ayant la priorité la plus élevée. Le plus haut niveau de priorité est celui du type de données défini par l’utilisateur.

COALESCE et ISNULL n’ont pas la même approche déterministe concernant le type de données du résultat retourné.

En effet, là où COALESCE retourne le type de données de l’expression ayant le niveau de priorité le plus élevé, ISNULL retourne le même type de données que celui du premier argument, peu importe son niveau de priorité.

Pour vous en convaincre :

  • Test de priorité de type de données avec COALESCE :
DECLARE @i INT=10, @m MONEY=50
SELECT COALESCE(@i,@m) AS 'COALESCE'
GO

Le résultat affiché sera le suivant :


  • Test de priorité de type de données avec ISNULL :
DECLARE @i INT=10, @m MONEY=50
SELECT ISNULL(@i,@m) AS 'ISNULL'
GO

Le résultat sera le suivant :


Comme on peut le souligner dans le cas de COALESCE, @i a adopté le même type de données que @m, c’est-à-dire MONEY. En effet, MONEY a un niveau de priorité plus élevé que le type INT. Dans le cas d’ISNULL remarque que contrairement au test avec COALESCE, il n’y a pas eu de conversion du type de données de @i. Cela confirme l’idée selon laquelle pour ISNULL, tout résultat retourné porte le même type de données que le premier argument passé en paramètre.

Les tests ont été effectués avec des types de données différents, mais issus d’une même famille (type de données numériques). Qu’en est-il du cas de types de données de famille différentes (i.e., alphanumériques vs numériques) ? En principe, la logique est la même que dans le cas des tests effectués précédemment, mais cependant, selon la position du type de données au sein de la fonction (1er ou 2ème argument), une erreur peut éventuellement être levée…

  • 2ème test de priorité de type de données avec COALESCE :
DECLARE @i INT=10, @v VARCHAR(10)='Varchar'
SELECT COALESCE(@v,@i) AS 'COALESCE'
--SELECT COALESCE(@i,@v) AS 'COALESCE'
GO

Résultat:


Si vous décommentez la seconde instruction de sélection, COALESCE retournera la valeur de @i.

  • 2ème test de priorité de type de données avec ISNULL :
DECLARE @i INT=10, @v VARCHAR(10)='Varchar'
SELECT ISNULL(@v,@i) AS 'ISNULL'
--SELECT ISNULL(@i,@v) AS 'ISNULL'
GO

Résultat:


@v étant le 1er argument, peu importe son type, sa famille et son niveau de priorité compare à @i, il est donc pris en compte dans le cas d’ISNULL. Pour preuve, si vous décommentez la seconde instruction de sélection, la valeur de @i sera retournée.

Et qu’en est-il des risques de troncation de résultats ? Ce risque existe suivant la volumétrie du type de données et le type de fonction utilisée. Pour être factuel :

  • 3ème test de priorité de type de données avec COALESCE :
DECLARE @v CHAR(5)
SELECT COALESCE(@v,'Washington') AS 'COALESCE'
GO

Résultat:


  • 3ème test de priorité de type de données avec ISNULL :
DECLARE @v CHAR(5)
SELECT ISNULL(@v,'Washington') AS 'ISNULL'
GO

Résultat:


Contrairement à COALESCE qui s’est adapté en comparant tous les types de données utilisés par ses arguments paramétrés, ISNULL a procédé à la troncature du résultat retourné en le limitant à la taille du type de données de son 1er argument passé en paramètre.

Validation de valeurs nulles

Si 2 arguments NULL sont utilisés, COALESCE et ISNULL travailleront également de manière différente :

  • Exemple avec COALESCE :
SELECT COALESCE(NULL,NULL) AS 'COALESCE'
GO

Résultat:


  • Exemple avec ISNULL :
SELECT ISNULL(NULL,NULL) AS 'ISNULL'
GO

Résultat:


Le fait qu’ISNULL ne retourne pas d’erreur, c’est parce qu’il prend NULL pour une valeur numérique (type de données INT). En guise de vérification, lançons le script suivant :

SELECT ISNULL(NULL,NULL) AS 'ISNULL'
 INTO Tab
GO
SELECT TYPE_NAME(user_type_id) AS 'TypeName'
FROM sys.columns
WHERE object_id=OBJECT_ID('Tab')
 AND name=N'ISNULL'
GO
DROP TABLE Tab
GO

Résultat :


Pour information, si l’on reprend l’exemple avec COALESCE et que l’on convertit le premier argument en un type de données existant (INT, par exemple) aucune erreur ne sera levée et la valeur NULL sera retournée :

SELECT COALESCE(CAST(NULL AS INT),NULL) AS 'COALESCE'
GO

Résultat:


NULLabilité

Dans le cas du stockage du résultat de l’utilisation d’un COALESCE ou d’un ISNULL au sein d’une table, il peut y avoir une différence suivant la NULLabilité ou non de la colonne cible. Le terme NULLabilité renvoie, ici, à une colonne pouvant accepter des valeurs nulles.

En cas d’utilisation de la fonction COALESCE, la colonne définie pour accueillir son résultat sera en NOT NULL si toutes les expressions de ladite fonction sont non-nulles (cas 1). Dans le cas échéant (cas 2), elle sera en NULL. Dans le cas d’ISNULL, la colonne sera en NOT NULL s’il y a une expression non-nulle (cas 3) et en NULL si toutes les 2 expressions sont nullables (cas 4).

DECLARE @v VARCHAR(5)
SELECT CoalesceColumn1=COALESCE(@v,'Washington'), -- cas 1
 CoalesceColumn2=COALESCE('Paris','Washington'), -- cas 2
 IsNullColumn1=ISNULL(@v,'Washington'), -- cas 3
 IsNullColumn2=ISNULL(@v,@v) -- cas 4
 INTO Tab
GO
SELECT name, typename=TYPE_NAME(system_type_id),is_nullable
FROM sys.columns
WHERE object_id=OBJECT_ID('dbo.Tab')
GO
DROP TABLE Tab
GO

Résultat:


Norme ANSI/ISO-SQL

Contrairement à ISNULL qui est une fonction du langage propriétaire T-SQL, COALESCE est issu de la norme ANSI/ISO-SQL.

Paramétrage

La fonction ISNULL est limitée à 2 arguments paramétrés là où COALESCE peut en supporter plusieurs (aucune limite n’a été officiellement documentée par Microsoft, à l’heure actuelle). Toutefois, il est possible de contourner les limites d’un ISNULL en faisant appel à plusieurs sous-ISNULL comme dans l’exemple ci-dessous (Emp est une table déjà créée avec 3 colonnes ID, Name et Age) :

SELECT COALESCE(ID,Name,Age)
FROM Emp
SELECT ISNULL(ID, ISNULL(Name, Age))
FROM Emp
GO

Performances

En règle générale, les performances entre un COALESCE et un ISNULL sont pratiquement les mêmes. Toutefois, il est possible de tomber sur des situations où ce n’est pas toujours le cas. Un cas classique est l’utilisation de sous-requêtes dans un COALESCE ou un ISNULL.

Pour être exact, supposons que l’on souhaite retourner le résultat d’une sous-requête quand l’expression écrite n’est pas nulle et un 0 le cas échéant.

Imaginons que l’on souhaite afficher la quantité maximale de transactions traitées (base de données exemple AdventureWorks) :

SET STATISTICS IO ON -- pour l'affichage des statistiques I/O
GO
USE AdventureWorks
GO
SELECT MAX(Quantity) FROM Production.TransactionHistory
GO

L’exécution de la requête donne les statistiques I/O suivantes :

Table ‘TransactionHistory’. Scan count 1, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Et accessoirement le plan d’exécution suivant:


Du côté de l’utilisation des fonctions COALESCE et ISNULL, cela donnerait :

  • Pour COALESCE :
SET STATISTICS IO ON -- pour l'affichage des statistiques I/O
GO
USE AdventureWorks
GO
SELECT COALESCE (SELECT MAX(Quantity) FROM Production.TransactionHistory),0) AS 'Max Quantity (COALESCE)'
GO

Côté statistiques I/O :

Table ‘TransactionHistory’. Scan count 2, logical reads 1594, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Côté plan d’exécution:


  • Pour ISNULL :
SET STATISTICS IO ON -- pour l'affichage des statistiques I/O
GO
USE AdventureWorks
GO
SELECT ISNULL((SELECT MAX(Quantity) FROM Production.TransactionHistory),0) AS 'Max Quantity (ISNULL)'
GO

Côté statistiques I/O :

Table ‘TransactionHistory’. Scan count 1, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Côté plan d’exécution:


Comme on peut le constater, le coût du traitement de la requête utilisant COALESCE est plus élevé que celle utilisant ISNULL. Cela s’explique par le fait que dans le cas d’un COALESCE, l’optimiseur SQL effectue en interne plusieurs opérations CASE (2 fois, dans notre cas, d’où le scan count à 2) pour vérifier chaque condition référençant la sous-requête : une fois pour vérifier si le résultat est nul et une autre fois pour retourner le résultat non-nul.

En résumé…

Il n’y a pas de règle empirique permettant de déterminer « à chaud » quelle fonction choisir entre COALESCE et ISNULL. Toutefois, à travers les quelques principales différences entre ces deux fonctions, il est possible de porter son choix sur COALESCE (respect de la norme ANSI-SQL, paramétrage plus large,…) ou ISNULL (sous-requêtes,…).

Publicités

2 commentaires sur “[SQL Server] T-SQL : COALESCE vs ISNULL

  1. F. BROUARD / SQLpro dit :

    Dans l’exemple de cout COALESCE / ISNULL tu as oublié le COALESCE dans la 1er requête !

    D’autre part la SQL est normalisé IOS (ANSI c’est US, une norme internationale doit préalablement est déposée nationalement); Les américain ayant tendance à tirer systématiquement la couverture à eux !!!

    A +

    • Salut,

      Erratum apporté. Je ne sais pas ce que j’ai fumé en omettant le COALESCE.

      Côté norme, j’ai rajouté ISO dans la section idoine, histoire de moins verser dans l’américanisme 😉

      Merci pour ton commentaire !

      M.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s