Database Options
This table is an alphabetic list of database options and corresponding SET and server options supported in Microsoft® SQL Server™ 2000.
| 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 | FALSE1 |
| 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 |
1 By default, autoclose is set to TRUE in SQL Server 2000 Desktop Engine.
The default database options for a new database are those defined in the model database. In new SQL Server installations, the settings in the model and master databases are the same.
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 with the USE statement in Transact-SQL and by both implicit and explicit means in other environments such as ODBC 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 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.