SET ANSI_NULLS（Transact-SQL）

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

指定与 SQL Server 中的值一起使用NULL时相等（=）和“不等于”（<>）比较运算符的 ISO 符合性行为。

• SET ANSI_NULLS ON- 计算值{expression} = NULL和{expression} <> NULLFalse值{expression}是否为 NULL。 此行为符合 ANSI。
• SET ANSI_NULLS OFF- 计算{expression} = NULL结果True{expression} <> NULL和值False{expression}是否为 NULL。 不建议使用此行为，因为 NULL 不应使用 = 值和 <> 运算符进行比较。

Transact-SQL 语法约定

语法

SQL Server、Azure Synapse Analytics 中的无服务器 SQL 池和 Microsoft Fabric 的语法

SET ANSI_NULLS { ON | OFF }

Azure Synapse Analytics 和 Analytics Platform System (PDW) 的语法

SET ANSI_NULLS ON

注解

当为 ON 时ANSI_NULLS，即使column_name中存在 NULL 值，SELECT使用该WHERE column_name = NULL语句也会返回零行。 SELECT即使column_name中存在非 NULL 值，使用该WHERE column_name <> NULL语句也会返回零行。

当ANSI_NULLS为 OFF 时，Equals （=） 和 Not Equal To （<>） 比较运算符不遵循 ISO 标准。 一个 SELECT 语句，该 WHERE column_name = NULL 语句返回 column_name中具有 null 值的行。 SELECT使用WHERE column_name <> NULL语句返回列中具有非NULL值的行。 此外， SELECT 使用 WHERE column_name <> XYZ_value 语句将返回不 XYZ_value 且不是 NULL的所有行。

如果 ANSI_NULLS 为 ON，则与 null 值的所有比较结果为 UNKNOWN。 当为 OFF 时 SET ANSI_NULLS ，如果数据值为 TRUE，则所有数据与 null 值的比较结果为 NULLTRUE。 如果未 SET ANSI_NULLS 指定，则应用当前数据库选项的设置 ANSI_NULLS 。 有关数据库选项的详细信息 ANSI_NULLS ，请参阅 ALTER DATABASE （Transact-SQL）。

下表显示了如何使用 ANSI_NULLS null 值和非 null 值设置影响布尔表达式的结果。

SET ANSI_NULLS ON仅当比较作数之一是变量或文本NULL时，NULL才会影响比较。 如果比较双方是列或复合表达式，则该设置不会影响比较。

若要使脚本按预期工作，无论 ANSI_NULLS 数据库选项或设置 SET ANSI_NULLS如何，都可以使用 IS NULL 并 IS NOT NULL 比较可能包含 null 值。

ANSI_NULLS 应设置为 ON 以执行分布式查询。

ANSI_NULLS 在计算列或索引视图上创建或更改索引时，还必须为 ON。 如果 SET ANSI_NULLS为 OFF，则计算列或索引视图上具有索引的表的任何CREATE或UPDATEINSERTDELETE语句都失败。 SQL Server 将返回一个错误消息，该错误消息会列出所有违反所需值的 SET 选项。 此外，执行 SELECT 语句（如果 SET ANSI_NULLS 为 OFF），SQL Server 将忽略计算列或视图上的索引值，并解析选择作，就像表或视图上没有此类索引一样。

连接时，SQL Server Native Client ODBC 驱动程序和 SQL Server Native Client OLE DB Provider for SQL Server 会自动设置为 ANSI_NULLS ON。 该设置可以在 ODBC 数据源、ODBC 连接属性或 OLE DB 连接属性（它们在连接到 SQL Server 实例之前在应用程序中设置）中进行配置。 SET ANSI_NULLS默认值为 OFF。

ANSI_NULLS启用启用时间ANSI_DEFAULTS。

设置 ANSI_NULLS 是在执行或运行时定义的，而不是在分析时定义的。

要查看此设置的当前设置，请运行以下查询：

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

权限

要求 公共 角色具有成员身份。

示例

下面的示例使用 Equals （=） 和 Not Equal To （<>） 比较运算符对变量中的或0值null进行比较NULL。

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

下表显示了结果。

所有 SET ANSI_NULLS ON 表达式的计算结果为“False”，因为 NULL 无法与 NULL 这些运算符进行比较或使用 0 这些运算符。

以下示例使用等于 (=) 和不等于 (<>) 比较运算符对表中的 NULL 值和非 NULL 值进行比较。 该示例还显示设置 SET ANSI_NULLS 不会影响 IS NULL。

-- 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 

现在将 ANSI_NULLS 设置为 ON 并测试。

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  

现在将 ANSI_NULLS 设置为 OFF 并测试。

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;  

相关内容

• SET 语句 (Transact-SQL)
• SESSIONPROPERTY （Transact-SQL）
• =（等于）(Transact-SQL)
• 如果。。。ELSE （Transact-SQL）
• <>（不等于）(Transact-SQL)
• SET ANSI_DEFAULTS（Transact-SQL）
• WHERE （Transact-SQL）
• WHILE （Transact-SQL）