SET ANSI_NULLS (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

SQL Server で NULL 値と共に使用される場合の、等しい (=) 比較演算子と等しくない (<>) 比較演算子の ISO 準拠の動作を指定します。

Note

SET ANSI_NULLS OFF および ANSI_NULLS OFF データベース オプションは非推奨です。 SQL Server 2017 (14.x) 以降では、ANSI_NULLSは常に ON に設定されます。 非推奨の機能を新しいアプリケーションで使用しないでください。 詳細については、「SQL Server 2017 の非推奨のデータベース エンジン機能」を参照してください。

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

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

解説

ANSI_NULLSが ON の場合、column_nameに NULL 値がある場合でも、使用する WHERE column_name = NULL Standard Edition LECT ステートメントは 0 行を返します。 column_nameに NULL 以外の値がある場合でも、使用する WHERE column_name <> NULL Standard Edition LECT ステートメントは 0 行を返します。

ANSI_NULLSが OFF の場合、等しい (=) および等しくない (<>) 比較演算子は ISO 標準に従いません。 使用する WHERE column_name = NULL Standard Edition LECT ステートメントは、column_name に null 値を持つ行を返します。 使用する Standard Edition LECT ステートメントはWHERE column_name <> NULL、列に NULL 以外の値を持つ行を返します。 また、Standard Edition LECT ステートメントを使用WHERE column_name <> XYZ_valueすると、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 値を利用したさまざまなブール式の結果に影響を与えるしくみを示しています。

ブール式	Standard Edition T ANSI_NULLS ON	Standard Edition T 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 は計算列やビュー上のインデックス値を無視し、テーブルやビューにそのようなインデックスがないものとして SELECT 操作を処理します。

注意

ANSI_NULLS は、計算列およびインデックス付きビューにおいてインデックスを操作するときに、指定された値に設定する必要がある 7 つの SET オプションの中の 1 つです。 オプション ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、QUOTED_IDENTIFIER、および CONCAT_NULL_YIELDS_NULL も ON に設定し、NUMERIC_ROUNDABORT を OFF に設定する必要があります。

SQL Server Native Client ODBC ドライバーおよび SQL Server Native Client OLE DB Provider for SQL Server では、接続時に自動的に 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;  

関連するコンテンツ

• SET ステートメント (Transact-SQL)
• SESSIONPROPERTY (Transact-SQL)
• = (等しい) (Transact-SQL)
• IF...ELSE (Transact-SQL)
• <> (等しくない) (Transact-SQL)
• SET ANSI_DEFAULTS (Transact-SQL)
• WHERE (Transact-SQL)
• WHILE (Transact-SQL)