SET ANSI_NULLS (Transact-SQL)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Spécifie le comportement conforme ISO des opérateurs de comparaison Equals (=) et Not Equal To (<>) lorsqu’ils sont utilisés avec des valeurs Null dans SQL Server.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server et Pool SQL serverless dans Azure Synapse Analytics, Microsoft Fabric

SET ANSI_NULLS { ON | OFF }

Syntaxe pour Azure Synapse Analytics et Analytics Platform System (PDW)

SET ANSI_NULLS ON

Notes

Lorsque ANSI_NULLS est ON, une instruction SELECT qui utilise WHERE column_name = NULL renvoie zéro ligne, même s’il existe des valeurs NULL dans column_name. Une instruction SELECT qui utilise WHERE column_name <> NULL renvoie zéro ligne, même s’il existe des valeurs non NULL dans column_name.

Lorsque ANSI_NULLS est OFF, les opérateurs de comparaison Equals (=) et Not Equal To (<>) ne suivent pas la norme ISO. Une instruction SELECT qui utilise WHERE column_name = NULL retourne les lignes qui ont des valeurs null dans column_name. Une instruction SELECT qui utilise WHERE column_name <> NULL retourne les lignes qui ont des valeurs non NULL dans la colonne. En outre, une instruction SELECT qui utilise WHERE column_name <> XYZ_value retourne toutes les lignes qui ne sont pas XYZ_value et qui ne sont pas NULL.

Quand ANSI_NULLS a la valeur ON, toutes les comparaisons effectuées sur une valeur Null retournent la valeur UNKNOWN. Lorsque la valeur de SET ANSI_NULLS a la valeur OFF, les comparaisons de toutes les données par rapport à une valeur Null sont vraies (TRUE) si la valeur des données est Null. Si SET ANSI_NULLS n'est pas spécifié, le paramètre de l'option ANSI_NULLS de la base de données actuelle s'applique. Pour plus d’informations sur l’option de base de données ANSI_NULLS, consultez ALTER DATABASE (Transact-SQL).

Le tableau suivant montre comment la valeur de ANSI_NULLS affecte les résultats de différentes expressions booléennes utilisant des valeurs Null et non-Null.

SET ANSI_NULLS ON n'a d'influence sur une comparaison que si l'un des opérandes de la comparaison est une variable Null ou une valeur Null littérale. Si les deux termes de la comparaison sont des colonnes ou des expressions composées, le paramètre n'a pas d'incidence sur la comparaison.

Pour qu'un script s'exécute correctement, quelle que soit la valeur de l'option de base de données ANSI_NULLS ou du paramètre de SET ANSI_NULLS, utilisez IS NULL et IS NOT NULL dans des comparaisons susceptibles de contenir des valeurs Null.

Lors de l'exécution de requêtes distribuées, l'option ANSI_NULLS doit être activée (ON).

ANSI_NULLS doit également avoir la valeur ON lors de la création ou de la modification d'index dans des colonnes calculées ou des vues indexées. Si SET ANSI_NULLS est désactivée (OFF), les instructions CREATE, UPDATE, INSERT et DELETE appliquées à des tables comportant des index sur des colonnes calculées ou des vues indexées échouent. SQL Server génère une erreur qui répertorie toutes les options SET non conformes aux valeurs requises. En outre, lors de l’exécution d’une instruction SELECT, si SET ANSI_NULLS a la valeur OFF, SQL Server ignore les valeurs d’index dans les vues ou les colonnes calculées et résout l’opération select comme si ces index n’existaient pas.

Le pilote ODBC de SQL Server Native Client et le fournisseur OLE DB de SQL Server Native Client pour SQL Server attribuent automatiquement la valeur ON à ANSI_NULLS lors de la connexion. Cette valeur peut être configurée dans les sources de données ou les attributs de connexion ODBC, ainsi que dans les propriétés de connexion OLE DB définies dans l'application avant la connexion à une instance de SQL Server. La valeur par défaut de SET ANSI_NULLS est OFF.

Quand ANSI_DEFAULTS est ON, ANSI_NULLS est activé.

La valeur d’ANSI_NULLS est définie au moment de l’exécution, pas au moment de l’analyse.

Pour afficher la valeur actuelle de ce paramètre, exécutez la requête suivante :

DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';  
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';  
SELECT @ANSI_NULLS AS ANSI_NULLS;   

Autorisations

Nécessite l'appartenance au rôle public .

Exemples

L'exemple ci-dessous utilise les opérateurs de comparaison Égal à (=) et Différent de (<>) pour effectuer une comparaison avec des valeurs NULL et non NULL dans une table. Il montre également que l’option IS NULL n’est pas influencée par le paramètre SET ANSI_NULLS.

-- Create table t1 and insert values.  
CREATE TABLE dbo.t1 (a INT NULL);  
INSERT INTO dbo.t1 values (NULL),(0),(1);  
GO  
  
-- Print message and perform SELECT statements.  
PRINT 'Testing default setting';  
DECLARE @varname int;   
SET @varname = NULL;  
  
SELECT a  
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO 

Définissez à présent ANSI_NULLS avec la valeur ON et effectuez un test.

PRINT 'Testing ANSI_NULLS ON';  
SET ANSI_NULLS ON;  
GO  
DECLARE @varname int;  
SET @varname = NULL  
  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  

Définissez à présent ANSI_NULLS avec la valeur OFF et effectuez un test.

PRINT 'Testing ANSI_NULLS OFF';  
SET ANSI_NULLS OFF;  
GO  
DECLARE @varname int;  
SET @varname = NULL;  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  
  
-- Drop table t1.  
DROP TABLE dbo.t1;  

Contenu connexe

• Instructions SET (Transact-SQL)
• SESSIONPROPERTY (Transact-SQL)
• = (Equals) (Transact-SQL)
• IF...ELSE (Transact-SQL)
• <> (Différent de) (Transact-SQL)
• SET ANSI_DEFAULTS (Transact-SQL)
• WHERE (Transact-SQL)
• WHILE (Transact-SQL)