SET ANSI_NULLS (Transact-SQL)

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

在 SQL Server 中搭配 Null 值使用時，指定 Equals （=） 和 Not Equal To （<>） 比較運算子的 ISO 兼容行為。

注意

SET ANSI_NULLS OFF 和 ANSI_NULLS OFF 資料庫選項已被取代。 從 SQL Server 2017 （14.x） 開始，ANSI_NULLS一律設定為 ON。 已被取代的功能不應在新應用程式中使用。 如需詳細資訊，請參閱 SQL Server 2017 中已被取代 資料庫引擎 功能。

Transact-SQL 語法慣例

Syntax

Azure Synapse Analytics、Microsoft Fabric 中的 SQL Server 無伺服器 SQL 集區語法

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 值的數據列。 使用 WHERE column_name <> NULL 的 SELECT 語句會傳回數據行中具有非 NULL 值的數據列。 此外，使用 WHERE column_name <> XYZ_value 的SELECT語句會傳回未 XYZ_value 且不是 NULL 的所有數據列。

當 ANSI_NULLS 是 ON 時，所有對於 Null 值的比較都會得出 UNKNOWN。 當 SET ANSI_NULLS 是 OFF 時，如果資料值是 NULL，所有對於 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 UNKNOWN true
1 = NULL UNKNOWN FALSE
NULL <> NULL UNKNOWN FALSE
1 <> NULL UNKNOWN true
NULL > NULL UNKNOWN UNKNOWN
1 > NULL UNKNOWN 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 設定為何，請在可能含有 Null 值的比較中，使用 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 連線屬性中設定這項設定，或在應用程式連線到 SQL Server 執行個體之前所設定的 OLE DB 連線屬性中設定這項設定。 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;

權限

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

現在將 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;

相關內容