This table is an alphabetic 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 |
| autoclose | None | None | FALSE |
| autoshrink | 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 |
| dbo use only | None | None | FALSE |
| default to local cursor | 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 |
| select into/ bulkcopy | None | None | FALSE |
| single user | None | None | FALSE |
| subscribed | None | None | TRUE |
| torn page detection | None | TRUE | |
| trunc. log on chkpt. | None | None | TRUE |
The default database options for a new database are those that have been defined in the model database. In new installations of SQL Server, the settings in the model and master databases are the same.
A change to a database option forces a recompile of everything that is in the cache.
The database context of scripts and the batches within scripts is determined by the most recent connection. The connection can be explicitly set with the USE statement in Transact-SQL and by both implicit and explicit means in other environments such as OBDC and OLE DB. For more information, see Choosing a Database.
When a stored procedure is executed from a batch or another stored procedure, it is executed under the option settings of the database in which it 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 that are in multiple databases, the current database context and the current connection context (the database defined by the USE statement if it is in a batch, or the database that contains the stored procedure if it is in a stored procedure) apply to that statement.