SET ANSI_NULLS (Transact-SQL)
適用対象: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL Server で NULL 値と共に使用される場合の Equals (=
) および Not Equal To (<>
) 比較演算子の ISO 準拠動作を指定します。
Note
SET ANSI_NULLS OFF
および ANSI_NULLS OFF データベース オプションは非推奨です。 SQL Server 2017 (14.x) 以降では、ANSI_NULLSは常に ON に設定されます。 非推奨の機能を新しいアプリケーションで使用しないでください。 詳細については、SQL Server 2017 のdeprecated データベース エンジン機能を参照してください。
構文
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 の場合、 WHERE column_name = NULL
を使用する SELECT ステートメントは、 column_nameに NULL 値がある場合でも 0 行を返します。 WHERE column_name <> NULL
を使用する SELECT ステートメントでは、column_nameに NULL 以外の値がある場合でも、0 行が返されます。
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 は計算列やビュー上のインデックス値を無視し、テーブルやビューにそのようなインデックスがないものとして 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;