SQL Server：如何全局默认开启ANSI_NULLS与QUOTED_IDENTIFIER及查看设置？

Absolutely, you can set ANSI_NULLS and QUOTED_IDENTIFIER to be enabled by default at both database and server levels—no more manually adding these settings to every script. Here's how to do it, plus how to check your current defaults:

(a) Database Level Configuration #

The database-level setting applies to all sessions connected to that database (unless a session explicitly overrides it). It also becomes the default for any new objects created in the database, as long as the creating session doesn't change the settings.

Check Current Database Defaults #

Run this query in the target database to see the current status:

SELECT 
    DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullsEnabled') AS DbAnsiNullsEnabled,
    DATABASEPROPERTYEX(DB_NAME(), 'IsQuotedIdentifiersEnabled') AS DbQuotedIdentifierEnabled;

A return value of 1 means the setting is enabled, 0 means disabled.

Set Database Defaults to Enabled #

Use ALTER DATABASE to turn these settings on permanently for the database:

-- Enable ANSI_NULLS
ALTER DATABASE [YourDatabaseName] 
SET ANSI_NULLS ON 
WITH ROLLBACK IMMEDIATE; -- Use this to force immediate application (terminates uncommitted transactions)

-- Enable QUOTED_IDENTIFIER
ALTER DATABASE [YourDatabaseName] 
SET QUOTED_IDENTIFIER ON 
WITH ROLLBACK IMMEDIATE;

If you don't want to terminate active transactions, replace WITH ROLLBACK IMMEDIATE with WITH NO_WAIT (fails if transactions are active) or omit the clause entirely (waits for transactions to finish).

(b) Server Level Configuration #

The server-level setting defines the default for newly created databases. Existing databases won't inherit this change automatically—you'll need to update them separately if needed.

Check Current Server Defaults #

You can use either sp_configure or server properties to check:
Option 1 (via sp_configure):

EXEC sp_configure 'user options';

Look at the config_value column:

• ANSI_NULLS corresponds to bit 1 (value 1)
• QUOTED_IDENTIFIER corresponds to bit 2 (value 2)
  If both are enabled, the value will be 3 (1+2).

Option 2 (more readable):

SELECT 
    SERVERPROPERTY('IsAnsiNullDefault') AS ServerAnsiNullsDefault,
    SERVERPROPERTY('IsQuotedIdentifierEnabled') AS ServerQuotedIdentifierDefault;

Set Server Defaults to Enabled #

Use sp_configure to update the server-wide user options:

-- Enable both ANSI_NULLS and QUOTED_IDENTIFIER (bitmask 1+2=3)
EXEC sp_configure 'user options', 3;
RECONFIGURE; -- Applies the change immediately

If you only want to enable one setting:

• For ANSI_NULLS only: EXEC sp_configure 'user options', 1; RECONFIGURE;
• For QUOTED_IDENTIFIER only: EXEC sp_configure 'user options', 2; RECONFIGURE;

Important Note #

When you create objects like stored procedures, functions, or triggers, SQL Server saves the ANSI_NULLS and QUOTED_IDENTIFIER settings from the creating session into the object's metadata. Even if you update the database/server defaults later, existing objects will retain their original settings. To fix this, you'll need to drop and recreate those objects with the new default settings active.

内容的提问来源于stack exchange，提问作者joedotnot