Database Options

The following table provides an alphabetical list of database options and corresponding SET and server options that are supported in Microsoft SQL Server.

Database option

SET option

Server

option

Default

setting

ANSI_NULL_DEFAULT

ANSI_NULL_DFLT_ON ANSI_NULL_DFLT_OFF

user options assigns a default.

OFF

ANSI_NULLS

ANSI_NULLS

user options assigns a default.

OFF

ANSI_WARNINGS

ANSI_WARNINGS

user options assigns a default.

OFF

AUTO_CREATE_STATISTICS

None

None

ON

AUTO_UPDATE_STATISTICS

None

None

ON

AUTO_CLOSE

None

None

FALSE1

AUTO_SHRINK

None

None

FALSE

CONCAT_NULL_YIELDS_NULL

CONCAT_NULL_YIELDS_NULL

None

OFF

CURSOR_CLOSE_ON_COMMIT

CURSOR_CLOSE_ON_COMMIT

user options assigns a default.

OFF

RESTRICTED_USER

None

None

FALSE

CURSOR_DEFAULT_LOCAL

None

None

FALSE

MERGE PUBLISH

None

None

FALSE

OFFLINE

None

None

FALSE

PUBLISHED

None

None

FALSE

QUOTED_IDENTIFIER

QUOTED_IDENTIFIER

user options assigns a default.

OFF

READ_ONLY

None

None

FALSE

RECURSIVE_TRIGGERS

None

None

FALSE

RECOVERY BULK_LOGGED

None

None

FALSE

SINGLE_USER

None

None

FALSE

SUBSCRIBED

None

None

TRUE

TORN_PAGE_DETECTION

 

None

TRUE

RECOVERY SIMPLE

None

None

TRUE

1 By default, AUTO_CLOSE is set to TRUE in SQL Server 2005 Express Edition.

The default database options for a new database are those defined in the model database. To see the default settings of the model database, see model Database.

A change to a database option forces a recompile of everything in the cache.

Options and Database Context

The database context of scripts and the batches within scripts is determined by the most recent connection. The connection can be explicitly set by using the USE statement in Transact-SQL and by using both implicit and explicit means in other environments, such as ODBC and OLE DB. For more information, see Selecting a Database.

When a stored procedure is executed from a batch or another stored procedure, the stored procedure is executed under the option settings of the database in which the procedure is stored. For example, when stored procedure db1.dbo.sp1 calls stored procedure db2.dbo.sp2, sp1 is executed under the current compatibility level setting of db1, and sp2 is executed under the current compatibility level setting of db2.

When a Transact-SQL statement refers to objects in multiple databases, the current database context and the current connection context apply to that statement.