SET ANSI_NULL_DFLT_ON (Transact-SQL)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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)