SET ANSI_NULLS (Transact-SQL)

發行項
03/07/2014

指定在 SQL Server 2014 中搭配 Null 值一起使用時，等於 (=) 和不等於 (<>) 比較運算子的 ISO 標準行為。

重要事項
在未來的 SQL Server 版本中，ANSI_NULLS 一定會是 ON，而且明確將此選項設定為 OFF 的應用程式將會產生錯誤。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

適用於：SQL Server (SQL Server 2008 至目前版本)。

主題連結圖示 Transact-SQL 語法慣例

語法

SET ANSI_NULLS { ON | OFF }

備註

當 SET ANSI_NULLS 是 ON 時，使用 WHERE column_name = NULL 的 SELECT 陳述式會傳回零個資料列，即使 column_name 含有 Null 值，也是如此。使用 WHERE column_name <> NULL 的 SELECT 陳述式也會傳回零個資料列，即使 column_name 含有非 Null 值，也是如此。

當 SET ANSI_NULLS 是 OFF 時，等於 (=) 和不等於 (<>) 比較運算子並不遵照 ISO 標準。使用 WHERE column_name = NULL 的 SELECT 陳述式會傳回 column_name 中含有 Null 值的資料列。使用 WHERE column_name<> NULL 的 SELECT 陳述式會傳回資料行中含有非 Null 值的資料列。另外，使用 WHERE column_name <> XYZ_value 的 SELECT 陳述式也會傳回非 XYZ_value 以及非 Null 值的所有資料列。

當 SET ANSI_NULLS 是 ON 時，所有對於 Null 值的比較都會得出 UNKNOWN。當 SET ANSI_NULLS 是 OFF 時，如果資料值是 NULL，所有對於 Null 值的資料比較都會得出 TRUE。如果未指定 SET ANSI_NULLS，就會套用目前資料庫的 ANSI_NULLS 選項設定。如需有關 ANSI_NULLS 資料庫選項的詳細資訊，請參閱＜ALTER DATABASE (Transact-SQL)＞。

只有在一個比較運算元是 NULL 變數或常值 NULL 變數時，SET ANSI_NULLS ON 才會影響比較。如果比較的兩端是資料行或複合運算式，設定就不會影響比較。

若要使指令碼的運作能夠符合預期，不論 ANSI_NULLS 資料庫選項或 SET ANSI_NULLS 設定為何，請在可能含有 Null 值的比較中，使用 IS NULL 和 IS NOT NULL。

SET ANSI_NULLS 應該設為 ON，以便執行分散式查詢。

當您建立或變更計算資料行索引或索引檢視表時，SET 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 連接屬性中設定這項設定，或在應用程式連接到 SQL Server 執行個體之前所設定的 OLE DB 連接屬性中設定這項設定。 SET ANSI_NULLS 的預設值為 OFF。

當 SET ANSI_DEFAULTS 是 ON 時，會啟用 SET ANSI_NULLS。

SET ANSI_NULLS 的設定是在執行階段進行設定，而不是在剖析階段進行設定。

若要檢視此設定的目前設定，請執行下列查詢。

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

權限

需要 public 角色中的成員資格。

範例

下列範例會利用等於 (=) 和不等於 (<>) 比較運算子，與資料表中的 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

-- SET ANSI_NULLS to ON and 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

-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET 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;