SET ANSI_NULLS (Transact-SQL)

Article
08/20/2013

Spécifie le comportement, compatible avec ISO, des opérateurs Égal à (=) et Différent de (<>), lorsqu'ils sont utilisés avec des valeurs Null.

Important

Dans une future version de SQL Server, ANSI_NULLS sera toujours ON et toute application qui définira explicitement l'option à OFF générera une erreur. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

Icône Lien de rubrique Conventions de syntaxe Transact-SQL

Syntaxe

SET ANSI_NULLS { ON | OFF }

Notes

Lorsque SET ANSI_NULLS a la valeur ON, une instruction SELECT qui utilise la clause WHERE column_name = NULL ne retourne aucune ligne, même si column_name comporte des valeurs Null. Une instruction SELECT qui utilise WHERE column_name <> NULL ne retourne aucune ligne, même si column_name contient des valeurs non Null.

Lorsque la valeur de SET ANSI_NULLS a la valeur OFF, les opérateurs de comparaison Égal à (=) et Différent de (<>) ne sont pas conformes à la norme ISO. Une instruction SELECT utilisant l'option WHERE column_name = NULL retourne les lignes de la colonne column_name contenant des valeurs Null. Une instruction SELECT utilisant l'option WHERE column_name <> = NULL retourne les lignes de la colonne contenant des valeurs non Null. De plus, une instruction SELECT utilisant WHERE column_name <> XYZ_value retourne toutes les lignes qui ne contiennent ni XYZ_value, ni la valeur Null.

Lorsque la valeur de SET ANSI_NULLS a la valeur ON, toutes les comparaisons effectuées sur une valeur Null retournent la valeur UNKNOWN (inconnu). 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).

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 SET ANSI_NULLS doit être activée (ON).

SET 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 et 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.

[!REMARQUE]

ANSI_NULLS est l'une des sept options SET qui doivent avoir les valeurs requises lors du traitement des index dans des colonnes calculées et des vues indexées. Les options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER et CONCAT_NULL_YIELDS_NULL doivent également avoir la valeur ON et NUMERIC_ROUNDABORT la valeur OFF.

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.

Lorsque SET ANSI_DEFAULTS a la valeur ON, l'option SET ANSI_NULLS est activée.

La valeur de SET ANSI_NULLS est définie lors de l'exécution, et non durant l'analyse.

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 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 t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO

-- Print message and perform SELECT statements.
PRINT 'Testing default setting'
DECLARE @varname int
SELECT @varname = NULL
SELECT * 
FROM t1 
WHERE a = @varname
SELECT * 
FROM t1 
WHERE a <> @varname
SELECT * 
FROM t1 
WHERE a IS NULL
GO

-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT * 
FROM t1 
WHERE a = @varname
SELECT * 
FROM t1 
WHERE a <> @varname
SELECT * 
FROM t1 
WHERE a IS NULL
GO

-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT * 
FROM t1 
WHERE a = @varname
SELECT * 
FROM t1 
WHERE a <> @varname
SELECT * 
FROM t1 
WHERE a IS NULL
GO

-- Drop table t1.
DROP TABLE t1