SET ANSI_NULLS (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 NULL des valeurs dans SQL Server.

• SET ANSI_NULLS ON - Évalue les deux {expression} = NULL et {expression} <> NULL comme False si la valeur de l’objet {expression} est NULL. Ce comportement est conforme à ANSI.
• SET ANSI_NULLS OFF- Évalue {expression} = NULL comme TrueFalse{expression} <> NULL si la valeur est .{expression}NULL Ce comportement n’est pas recommandé, car les valeurs ne doivent pas être comparées à l’utilisation NULL= et <> aux opérateurs.

Remarque

SET ANSI_NULLS OFF et l’option ANSI_NULLS OFF de base de données est déconseillée. À compter de SQL Server 2017 (14.x), ANSI_NULLS est toujours défini sur ON. Les fonctionnalités dépréciées ne doivent pas être utilisées dans de nouvelles applications. Pour plus d’informations, consultez Fonctionnalités de Moteur de base de données déconseillées dans SQL Server 2017.

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

Lorsqu’il ANSI_NULLS s’agit de ON, une SELECT instruction qui utilise WHERE column_name = NULL retourne zéro ligne, même s’il existe des valeurs NULL dans column_name. Une SELECT instruction qui utilise WHERE column_name <> NULL retourne 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 SELECT instruction qui utilise WHERE column_name = NULL retourne les lignes qui ont des valeurs null dans column_name. Une SELECT instruction qui utilise WHERE column_name <> NULL retourne les lignes qui n’ont pas deNULL valeurs dans la colonne. En outre, une SELECT instruction qui utilise WHERE column_name <> XYZ_value retourne toutes les lignes qui ne sont pas XYZ_value et qui ne sont pas NULL.

Lorsqu’il ANSI_NULLS s’agit de ON, toutes les comparaisons par rapport à une valeur Null sont évaluées à UNKNOWN. Quand SET ANSI_NULLS est DÉSACTIVÉ, les comparaisons de toutes les données par rapport à une valeur Null ont la valeur TRUE si la valeur de données est NULL. S’il SET ANSI_NULLS n’est pas spécifié, le paramètre de l’option ANSI_NULLS de la base de données active s’applique. Pour plus d’informations sur l’option ANSI_NULLS de base de données, consultez ALTER DATABASE (Transact-SQL).

Le tableau suivant montre comment le paramètre affecte ANSI_NULLS les résultats des expressions booléennes à l’aide de valeurs null et non null.

Expression booléenne	SET ANSI_NULLS ON	SET ANSI_NULLS OFF
NULL = NULL	INCONNU	true
1 = NULL	INCONNU	FAUX
NULL <> NULL	INCONNU	FAUX
1 <> NULL	INCONNU	true
NULL > NULL	INCONNU	INCONNU
1 > NULL	INCONNU	INCONNU
NULL IS NULL	true	true
1 IS NULL	FAUX	FAUX
NULL IS NOT NULL	FAUX	FAUX
1 IS NOT NULL	true	true

SET ANSI_NULLS ON affecte une comparaison uniquement si l’un des opérandes de la comparaison est une variable qui est NULL ou un littéral NULL. 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 fonctionne comme prévu, quelle que soit l’option ANSI_NULLS de base de données ou le paramètre , SET ANSI_NULLSutilisez IS NULL et IS NOT NULL dans des comparaisons qui peuvent contenir des valeurs Null.

ANSI_NULLS doit être défini sur ON pour l’exécution de requêtes distribuées.

ANSI_NULLS doit également être ACTIVÉ lorsque vous créez ou modifiez des index sur des colonnes calculées ou des vues indexées. Si SET ANSI_NULLS est OFF, any CREATE, UPDATE, INSERT, et DELETE instructions on tables with indexes on computed columns or indexed views fail. SQL Server génère une erreur qui répertorie toutes les options SET non conformes aux valeurs requises. En outre, lorsque vous exécutez une SELECT instruction, si SET ANSI_NULLS elle est OFF, SQL Server ignore les valeurs d’index sur les colonnes ou les vues calculées et résout l’opération de sélection comme s’il n’y avait pas d’index de ce type sur les tables ou les vues.

Remarque

ANSI_NULLS est l’une des sept options SET qui doivent être définies sur les valeurs requises lors du traitement des index sur des colonnes calculées ou des vues indexées. Les options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER et CONCAT_NULL_YIELDS_NULL doivent également être définies sur ON, et NUMERIC_ROUNDABORT doit être définie sur OFF.

Le pilote ODBC SQL Server Native Client et le fournisseur OLE DB SQL Server Native Client pour SQL Server sont automatiquement définis ANSI_NULLS sur ON 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 SET ANSI_NULLS est OFF.

Quand ANSI_DEFAULTS on est ACTIVÉ, ANSI_NULLS est activé.

Le paramètre est défini au moment de ANSI_NULLS l’exécution ou de l’exécution et non 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 suivant utilise les opérateurs de comparaison Equals (=) et Not Equal To (<>) pour effectuer des comparaisons avec NULL ou 0 avec la null valeur d’une variable.

SET ANSI_NULLS OFF
DECLARE @var INT = NULL
SELECT
    IIF(@var = NULL,  'True', 'False') as EqualNull,
    IIF(@var <> NULL, 'True', 'False') as DifferentNull,
    IIF(@var = 0,     'True', 'False') as EqualZero,
    IIF(@var <> 0,    'True', 'False') as DifferentZero

Les résultats sont affichés dans le tableau suivant.

EqualNull	DifferentNull	EqualZero	DifferentZero
Vrai	Faux	Faux	Vrai

Toutes SET ANSI_NULLS ON les expressions sont évaluées comme « False », car NULL elles ne peuvent pas être comparées à NULL ces opérateurs ou 0 en utilisant ces opérateurs.

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. L’exemple montre également que le paramètre n’affecte SET ANSI_NULLSIS NULLpas .

-- 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)
• = (Égal) (Transact-SQL)
• SI... ELSE (Transact-SQL)
• <> (Différent de) (Transact-SQL)
• SET ANSI_DEFAULTS (Transact-SQL)
• WHERE (Transact-SQL)
• WHILE (Transact-SQL)