Export (0) Print
Expand All

Database Options

SQL Server 2000

  New Information - SQL Server 2000 SP3.

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
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
db chaining None Cross DB Ownership Chaining determines if the server observes this option 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 (MSDE 2000).

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.

© 2014 Microsoft