SQL Server - NULL, or NOT NULL : that is the question !

Il arrive assez souvent de constater dans les script de création de tables (CREATE TABLE) ou de modification de structures de tables (ALTER TABLE), que certaines colonnes ne sont dotées d'aucune mention précisant leur caractère nullable ou non-nullable.
Généralement, seules les colonnes non-nullables sont précisées expressément dans les scripts par la mention NOT NULL.
Ci-dessous un exemple classique du genre :

CREATE TABLE dbo.Client 
  (Id        INT  NOT NULL CONSTRAINT PK_Client PRIMARY KEY,  
   Nom       VARCHAR(50) NOT NULL,   
   Prenom    VARCHAR(50),                           
   Categorie VARCHAR(10));

Comme vous pouvez le constater, dans l'exemple ci-dessus, aucune précision n'a été donnée concernant le caractère nullable ou non-nullable des 2 colonnes Prenom et Categorie. Seule la colonne Nom est spécifiée explicitement NOT NULL, en plus, et cela va de soi, de la colonne Id en tant que clé primaire.

Beaucoup pensent, à tort, que le fait de ne rien préciser est équivaut à NULL (colonne nullable), et considèrent qu'il n'est pas nécessaire de surcharger les scripts avec du texte inutile (mention NULL) ! Et bien, ils ont tort, et c'est une grave erreur.
En réalité, en suivant la mauvaise pratique illustrée au travers l'exemple ci-dessus, vous n'avez aucune garantie que les colonnes Prenom et Categorie accepteront les valeurs NULL. Les 2 colonnes Prenom et Categorie peuvent très bien être créées avec la contrainte NOT NULL ! Ce qui va vraisemblablement à l'encontre de ce que vous souhaitiez ! Je vous donnerai un exemple de cette situation un peu plus loin dans cet article.

En effet, lorsque vous créez ou modifiez une table à l'aide des instructions CREATE TABLE ou ALTER TABLE, les paramètres de la base de données et de la session influencent et éventuellement modifient la possibilité d'accepter ou non les valeurs NULL dans une définition de colonne.

Lorsque vous ne spécifiez pas explicitement, le caractère nullable (NULL) ou non-nullable (NOT NULL) pour une colonne, les règles suivantes s'appliquent :
Lorsque les paramètres de session ont pour valeur ON en utilisant SET :
ANSI_NULL_DFLT_ON = ON, le caractère NULL est affecté à la colonne (colonne nullable).
ANSI_NULL_DFLT_OFF = ON, le caractère NOT NULL est affecté à la colonne (colonne non-nullable).

Lorsque les paramètres de base de données sont configurés en utilisant ALTER DATABASE :
ANSI_NULL_DEFAULT_ON = ON, le caractère NULL est affecté à la colonne.
ANSI_NULL_DEFAULT_OFF = ON, le caractère NOT NULL est affecté à la colonne.
Pour voir le paramètre de la base de données pour ANSI_NULL_DEFAULT, utilisez la vue système du catalogue sys.databases.

SELECT name, is_ansi_null_default_on FROM sys.databases

Ci-dessous un exemple de situation ou contexte où les colonnes pour lesquelles aucune précision (NULL ou NOT NULL) n'a été fournie, seront créées avec la contrainte NOT NULL et non pas en tant que colonnes nullables !

ALTER DATABASE [MaBase] SET ANSI_NULL_DEFAULT OFF WITH NO_WAIT
GO
SET ANSI_NULL_DFLT_ON OFF 
GO
DROP table dbo.Client 
GO 
CREATE TABLE dbo.Client 
  (Id        INT  NOT NULL CONSTRAINT PK_Client PRIMARY KEY,  
   Nom       VARCHAR(50) NOT NULL,   
   Prenom    VARCHAR(50),                           
   Categorie VARCHAR(10));

On tente d'insérer une ligne dans la table dbo.Client

INSERT INTO dbo.Client 
  (Id, Nom) 
values 
   (1, 'Cantor');

Résultat :

Msg 515, Niveau 16, État 2, Ligne 1
Impossible d'insérer la valeur NULL dans la colonne 'Prenom', table 'MaBase.dbo.Client'. Cette colonne n'accepte pas les valeurs NULL. Échec de INSERT.
L'instruction a été arrêtée.

Maintenant, si vous consultez, sous SSMS, la structure de la table dbo.Client, vous seriez surpris de voir que les 2 colonnes Prenom et Categorie ont été créées avec la contrainte NOT NULL, ce qui explique le message d'erreur généré par l'instruction INSERT ci-dessus :

Conclusion

Il est donc fortement recommandé de toujours définir explicitement une colonne comme NULL ou NOT NULL ou, si vous utilisez un type de données défini par l'utilisateur, d'autoriser la colonne à utiliser la possibilité de valeur NULL par défaut pour ce type de données.

Donc, si vous manifestez votre volonté de rédiger les scripts SQL dans les règles de l'art, n'oubliez pas désormais de réécrire l'exemple du script ci-haut, comme suit :

CREATE TABLE dbo.Client 
  (Id        INT  NOT NULL CONSTRAINT PK_Client PRIMARY KEY,  
   Nom       VARCHAR(50) NOT NULL,   
   Prenom    VARCHAR(50) NULL,                           
   Categorie VARCHAR(10) NULL);