SET ANSI_NULLS (Transact-SQL)
适用于:SQL Server
Azure SQL 数据库
Azure SQL 托管实例
Azure Synapse Analytics
Analytics Platform System (PDW)
指定在 SQL Server 中将相等值与 null 值一起使用时相等(=
)和“不等于”(<>
)比较运算符的 ISO 符合性行为。
注意
SET ANSI_NULLS OFF
和ANSI_NULLS OFF 数据库选项已弃用。 从 SQL Server 2017(14.x)开始,ANSI_NULLS始终设置为 ON。 在新的应用程序中不应使用已弃用的功能。 有关详细信息,请参阅 SQL Server 2017 中弃用的数据库引擎功能。
语法
SQL Server、Azure Synapse Analytics 中的无服务器 SQL 池和 Microsoft Fabric 的语法
SET ANSI_NULLS { ON | OFF }
Azure Synapse Analytics 和 Analytics Platform System (PDW) 的语法
SET ANSI_NULLS ON
注意
若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法,请参阅早期版本文档。
注解
当ANSI_NULLS为 ON 时,即使column_name中存在 NULL 值,使用 WHERE column_name = NULL
SELECT 语句也会返回零行。 即使column_name中存在非 NULL 值,使用 WHERE column_name <> NULL
SELECT 语句也会返回零行。
当ANSI_NULLS为 OFF 时,Equals (=
) 和 Not Equal To (<>
) 比较运算符不遵循 ISO 标准。 使用 WHERE column_name = NULL
SELECT 语句返回column_name中具有 null 值的行。 一个 SELECT 语句,该语句返回 WHERE column_name <> NULL
列中具有非 NULL 值的行。 此外,使用 WHERE column_name <> XYZ_value
SELECT 语句将返回不 XYZ_value 且非 NULL 的所有行。
当 ANSI_NULLS 为 ON 时,所有对 null 值的比较均取值为 UNKNOWN。 当 SET ANSI_NULLS 为 OFF 时,如果数据值为 NULL,则所有数据对空值的比较将取值为 TRUE。 如果未指定 SET ANSI_NULLS,则应用当前数据库的 ANSI_NULLS 选项设置。 有关 ANSI_NULLS 数据库选项的详细信息,请参阅 ALTER DATABASE (Transact-SQL)。
下表显示 ANSI_NULLS 的设置如何通过 NULL 值和非 NULL 值影响大量布尔表达式的结果。
布尔表达式 | SET ANSI_NULLS ON | SET ANSI_NULLS OFF |
---|---|---|
NULL = NULL |
未知 | true |
1 = NULL |
未知 | FALSE |
NULL <> NULL |
UNKNOWN | FALSE |
1 <> NULL |
UNKNOWN | true |
NULL > NULL |
UNKNOWN | UNKNOWN |
1 > NULL |
UNKNOWN | 未知 |
NULL IS NULL |
TRUE | TRUE |
1 IS NULL |
FALSE | FALSE |
NULL IS NOT NULL |
FALSE | FALSE |
1 IS NOT NULL |
TRUE | TRUE |
仅当某个比较操作数是值为 NULL 的变量或文字 NULL 时,SET ANSI_NULLS ON 才会影响比较。 如果比较双方是列或复合表达式,则该设置不会影响比较。
为使脚本按预期运行,不管 ANSI_NULLS 数据库选项或 SET ANSI_NULLS 的设置如何,请在可能包含空值的比较中使用 IS NULL 和 IS NOT NULL。
在执行分布式查询时应将 ANSI_NULLS 设置为 ON。
针对计算列或索引视图创建或更改索引时,ANSI_NULLS 也必须为 ON。 如果 SET ANSI_NULLS 为 OFF,则针对表(包含计算列或索引视图的索引)的 CREATE、UPDATE、INSERT 和 DELETE 语句将失败。 SQL Server 将返回一个错误消息,该错误消息会列出所有违反所需值的 SET 选项。 另外,在执行 SELECT 语句时,如果 SET ANSI_NULLS 为 OFF,则 SQL Server 忽略计算列或视图的索引值并解析选择操作,就好像表或视图没有这样的索引一样。
备注
ANSI_NULLS 是在处理计算列或索引视图的索引时必须设置为所需值的七个 SET 选项之一。 此外,ANSI_PADDING
、ANSI_WARNINGS
、ARITHABORT
、QUOTED_IDENTIFIER
和 CONCAT_NULL_YIELDS_NULL
选项还必须设置为 ON,而 NUMERIC_ROUNDABORT
必须设置为 OFF。
进行连接时,SQL Server 的 SQL Server Native Client ODBC 驱动程序和 SQL Server Native Client OLE DB 访问接口会自动将 ANSI_NULLS 设置为 ON。 该设置可以在 ODBC 数据源、ODBC 连接属性或 OLE DB 连接属性(它们在连接到 SQL Server 实例之前在应用程序中设置)中进行配置。 SET ANSI_NULLS 的默认值为 OFF。
ANSI_DEFAULTS 为 ON 时,将启用 ANSI_NULLS。
ANSI_NULLS 的设置是在执行或运行时定义的,而不是在分析时定义的。
要查看此设置的当前设置,请运行以下查询:
DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';
SELECT @ANSI_NULLS AS ANSI_NULLS;
权限
要求 公共 角色具有成员身份。
示例
以下示例使用等于 (=
) 和不等于 (<>
) 比较运算符对表中的 NULL
值和非 NULL 值进行比较。 该示例还表明,IS NULL
不受 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
现在将 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;