SET ANSI_NULL_DFLT_ON (Transact-SQL)

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric のSQL 分析エンドポイント Microsoft Fabric のウェアハウス

セッションの動作を変更して、データベースの ANSI null default オプションが false である場合の、新しい列の NULL 値に関する既定の設定をオーバーライドします。 ANSI null default の値の設定について詳しくは、「ALTER DATABASE (Transact-SQL)」を参照してください。

Transact-SQL 構文表記規則

構文

-- Syntax for SQL Server and Azure SQL Database and Microsoft Fabric

SET ANSI_NULL_DFLT_ON {ON | OFF}

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse

SET ANSI_NULL_DFLT_ON ON

Note

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

解説

この設定は、新しい列に対する NULL 値の許容にのみ影響を与えます。また、この影響が生じるのは、CREATE TABLE および ALTER TABLE ステートメントで、列に対して NULL 値の許容が設定されていない場合です。 SET ANSI_NULL_DFLT_ON が ON の場合、ALTER TABLE ステートメントと CREATE TABLE ステートメントで作成される新しい列は、列の NULL 値の許容状態を明示的に指定しない場合に NULL 値が許可されます。 NULL または NOT NULL を明示的に指定して作成した列に対して、SET ANSI_NULL_DFLT_ON は影響しません。

ANSI_NULL_DFLT_ON と ANSI_NULL_DFLT_OFF の両方を同時に ON に設定することはできません。 一方のオプションが ON に設定されている場合、他方のオプションは OFF に設定されます。 つまり、SET ANSI_NULL_DFLT_OFF と ANSI_NULL_DFLT_ON のいずれか一方を ON に設定するか、または両方を OFF に設定することだけが可能です。 いずれかのオプションが ON の場合、その設定 (SET ANSI_NULL_DFLT_OFF または SET ANSI_NULL_DFLT_ON) が有効になります。 両方のオプションを OFF に設定した場合、SQL Server では sys.databases カタログ ビューにある is_ansi_null_default_on 列の値が使用されます。

Transact-SQL スクリプトを、NULL 値の許容に関する設定が異なっているデータベースで使用する場合、このスクリプトによる動作の信頼性を高めるには、CREATE TABLE と ALTER TABLE のステートメントで NULL または NOT NULL を指定することをお勧めします。

SQL Server Native Client ODBC ドライバーおよび SQL Server Native Client OLE DB Provider for SQL Server では、接続時に自動的に ANSI_NULL_DFLT_ON が ON に設定されます。 DB-Library アプリケーションからの接続に対しては、既定では SET ANSI_NULL_DFLT_ON は OFF に設定されています。

SET ANSI_DEFAULTS が ON の場合には、SET ANSI_NULL_DFLT_ON は有効になります。

SET ANSI_NULL_DFLT_ON は、解析時ではなく実行時に設定されます。

SELECT INTO ステートメントを使用してテーブルを作成した場合は、SET ANSI_NULL_DFLT_ON の設定は適用されません。

この設定の現在の設定を表示するには、次のクエリを実行します。

DECLARE @ANSI_NULL_DFLT_ON VARCHAR(3) = 'OFF';  
IF ( (1024 & @@OPTIONS) = 1024 ) SET @ANSI_NULL_DFLT_ON = 'ON';  
SELECT @ANSI_NULL_DFLT_ON AS ANSI_NULL_DFLT_ON;  
  

アクセス許可

ロール public のメンバーシップが必要です。

例

次の例では、ANSI null default データベース オプションに 2 種類の設定を使用した場合の SET ANSI_NULL_DFLT_ON の効果を示します。

USE AdventureWorks2022;  
GO  
  
-- The code from this point on demonstrates that SET ANSI_NULL_DFLT_ON  
-- has an effect when the 'ANSI null default' for the database is false.  
-- Set the 'ANSI null default' database option to false by executing  
-- ALTER DATABASE.  
ALTER DATABASE AdventureWorks2022 SET ANSI_NULL_DEFAULT OFF;  
GO  
-- Create table t1.  
CREATE TABLE t1 (a TINYINT) ;  
GO   
-- NULL INSERT should fail.  
INSERT INTO t1 (a) VALUES (NULL);  
GO  
  
-- SET ANSI_NULL_DFLT_ON to ON and create table t2.  
SET ANSI_NULL_DFLT_ON ON;  
GO  
CREATE TABLE t2 (a TINYINT);  
GO   
-- NULL insert should succeed.  
INSERT INTO t2 (a) VALUES (NULL);  
GO  
  
-- SET ANSI_NULL_DFLT_ON to OFF and create table t3.  
SET ANSI_NULL_DFLT_ON OFF;  
GO  
CREATE TABLE t3 (a TINYINT);  
GO  
-- NULL insert should fail.  
INSERT INTO t3 (a) VALUES (NULL);  
GO  
  
-- The code from this point on demonstrates that SET ANSI_NULL_DFLT_ON   
-- has no effect when the 'ANSI null default' for the database is true.  
-- Set the 'ANSI null default' database option to true.  
ALTER DATABASE AdventureWorks2022 SET ANSI_NULL_DEFAULT ON  
GO  
  
-- Create table t4.  
CREATE TABLE t4 (a TINYINT);  
GO   
-- NULL INSERT should succeed.  
INSERT INTO t4 (a) VALUES (NULL);  
GO  
  
-- SET ANSI_NULL_DFLT_ON to ON and create table t5.  
SET ANSI_NULL_DFLT_ON ON;  
GO  
CREATE TABLE t5 (a TINYINT);  
GO   
-- NULL INSERT should succeed.  
INSERT INTO t5 (a) VALUES (NULL);  
GO  
  
-- SET ANSI_NULL_DFLT_ON to OFF and create table t6.  
SET ANSI_NULL_DFLT_ON OFF;  
GO  
CREATE TABLE t6 (a TINYINT);  
GO   
-- NULL INSERT should succeed.  
INSERT INTO t6 (a) VALUES (NULL);  
GO  
  
-- Set the 'ANSI null default' database option to false.  
ALTER DATABASE AdventureWorks2022 SET ANSI_NULL_DEFAULT ON;  
GO  
  
-- Drop tables t1 through t6.  
DROP TABLE t1,t2,t3,t4,t5,t6;  

参照

ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
SET ステートメント (Transact-SQL)
SET ANSI_DEFAULTS (Transact-SQL)
SET ANSI_NULL_DFLT_OFF (Transact-SQL)