SET ANSI_NULLS (Transact-SQL)

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

Important :
Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

Conventions de la syntaxe de 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 SQL-92. 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 la XYZ_value, ni la valeur NULL.

Remarque :

La façon dont SQL Server interprète une chaîne vide, comme un espace unique ou comme une vraie chaîne vide, dépend de la valeur du niveau de compatibilité. Si le niveau de compatibilité est inférieur ou égal à 65, SQL Server interprète les chaînes vides comme des espaces simples. Si le niveau de compatibilité est égal à 70, SQL Server interprète les chaînes vides comme telles. Pour plus d'informations sur la définition du niveau de compatibilité, consultez sp_dbcmptlevel (Transact-SQL).

Lorsque la valeur de SET ANSI_NULLS a la valeur ON, toutes les comparaisons effectuées sur une valeur NULL renvoient 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) et Définition des options de base de données.

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 a la valeur OFF, toute instruction CREATE, UPDATE, INSERT et DELETE dans des tables comportant des index sur des colonnes calculées ou des vues indexées échoue. 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 Native Client et le fournisseur OLE DB de SQL 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 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 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

Voir aussi

Référence

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

Aide et Informations

Assistance sur SQL Server 2005