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.
See Also