• Article

Setting Database Options

Several database-level options that determine the characteristics of the database can be set for each database. These options are unique to each database and do not affect other databases. These database options are set to default values when you create a database and can be changed by using the SET clause of the ALTER DATABASE statement. In addition, SQL Server Management Studio can be used to set most of these options.

Note

Server-wide settings are set using the sp_configure system stored procedure or SQL Server Management Studio. For more information, see Setting Server Configuration Options. Connection-level settings are specified by using SET statements. For more information, see SET Options.

To change the default values for any one of the database options for all newly created databases, change the appropriate database option in the model database. For example, if you want the default setting of the AUTO_CLOSE database option to be True for any new databases subsequently created, set the AUTO_CLOSE option for model to True.

After you set a database option, a checkpoint is automatically issued that causes the modification to take effect immediately. For more information, see CHECKPOINT (Transact-SQL).

Database Options

The following tables list the database options that are set when a database is created and their default values. For a complete description of these options, see ALTER DATABASE (Transact-SQL).

Auto Options

Control certain automatic behaviors.

Option

Description

Default value

AUTO_CLOSE

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

When set to OFF, the database remains open after the last user exits.

True for all databases when using SQL Server 2000 Desktop Engine or SQL Server Express, and False for all other editions, regardless of operating system.

AUTO_CREATE_STATISTICS

When set to ON, statistics are automatically created on columns used in a predicate.

When set to OFF, statistics are not automatically created; instead, statistics can be manually created.

True

AUTO_UPDATE_STATISTICS

When set to ON, any missing statistics required by a query for optimization are automatically built during query optimization.

When set to OFF, statistics must be manually created. For more information, see Using Statistics to Improve Query Performance.

True

AUTO_SHRINK

When set to ON, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. AUTO_SHRINK reduces the size of the transaction log only if the database is set to SIMPLE recovery model or if the log is backed up.

When set to OFF, database files are not automatically shrunk during periodic checks for unused space.

False

Auto_Update_Statistics_Asynchronously

When True, updates the statistics asynchronously.

False

Cursor Options

Control cursor behavior and scope.

Option

Description

Default value

CURSOR_CLOSE_ON_COMMIT

When set to ON, any cursors open when a transaction is committed or rolled back are closed.

When set to OFF, remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

OFF

CURSOR_DEFAULT

When LOCAL is specified and a cursor is not defined as GLOBAL when created, the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is valid only within this scope.

When GLOBAL is specified, and a cursor is not defined as LOCAL when created, the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection.

GLOBAL

Database Availability Options

Control whether the database is online or offline, who can connect to the database, and whether the database is in read-only mode.

Option

Description

Default value

OFFLINE | ONLINE | EMERGENCY

When OFFLINE is specified, the database is closed and shutdown cleanly and marked offline.

When ONLINE is specified, the database is open and available for use.

When EMERGENCY is specified, database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.

ONLINE

READ_ONLY | READ_WRITE

When READ_ONLY is specified, users can read data from the database but not modify it.

When READ_WRITE is specified, the database is available for read-and-write operations.

READ_WRITE

SINGLE_USER | RESTRICTED_USER | MULTI_USER

When SINGLE_USER is specified, one user at a time is allowed to connect to the database. All other user connections are broken.

When RESTRICTED_USER is specified, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.

When MULTI_USER is specified, all users that have the appropriate permissions to connect to the database are allowed.

MULTI_USER

Date Correlation Optimization Options

Control the date_correlation_optimization option.

Option

Description

Default value

DATE_CORRELATION_OPTIMIZATION

When ON is specified, SQL Server maintains correlation statistics between any two tables in the database that are linked by a FOREIGN KEY constraint and have datetime columns.

When OFF is specified, correlation statistics are not maintained.

OFF

For more information, see Optimizing Queries That Access Correlated datetime Columns.

External Access Options

Control whether the database can be accessed by external resources such as objects from another database.

Option

Description

Default value

DB_CHAINING

When ON is specified, the database can be the source or target of a cross-database ownership chain.

When OFF is specified, the database cannot participate in cross-database ownership chaining.

OFF

TRUSTWORTHY

When ON, database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.

When OFF is specified, in an impersonation context cannot access resources outside the database.

TRUSTWORTHY is set to OFF whenever the database is attached.

OFF

Parameterization Option

Controls the parameterization option.

Option

Description

Default value

PARAMETERIZATION

When SIMPLE specified, queries are parameterized based on the default behavior of the database

When FORCED is specified, SQL Server parameterizes all queries in the database.

SIMPLE

Recovery Options

Control the recovery model for the database.

Option

Description

Default value

RECOVERY

When FULL is specified, full recovery after media failure is provided by using transaction log backups. If a data file is damaged, media recovery can restore all committed transactions.

When BULK_LOGGED is specified, recovery after media failure by combining the best performance and least amount of log-space use for certain large-scale or bulk operations is provided.

When SIMPLE is specified, a simple backup strategy is provided that uses minimal log space.

FULL

PAGE_VERIFY

When CHECKSUM is specified, the Database Engine calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header.

When TORN_PAGE_DETECTION is specified, a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.

When NONE is specified, database page writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value. SQL Server will not verify a checksum or torn page during a read even if a CHECKSUM or TORN_PAGE_DETECTION value is present in the page header.

CHECKSUM

Service Broker Options

Control Service Broker options.

Option

Description

Default value

ENABLE_BROKER | DISABLE_BROKER | NEW_BROKER | ERROR_BROKER_CONVERSATIONS

When ENABLE_BROKER is specified, Service Broker is enabled for the specified database.

When DISABLE_BROKER is specified, Service Broker is disabled for the specified database.

When NEW_BROKER is specified, the database receives a new broker identifier.

When ERROR_BROKER_CONVERSATIONS is specified, conversations in the database receive an error message when the database is attached.

ENABLE_BROKER

Snapshot Isolation Options

Determine the transaction isolation level.

Option

Description

Default value

ALLOW_SNAPSHOT_ISOLATION

When ON is specified, transactions can specify the SNAPSHOT transaction isolation level. When a transaction runs at the SNAPSHOT isolation level, all statements see a snapshot of data as it exists at the start of the transaction.

When OFF is specified, transactions cannot specify the SNAPSHOT transaction isolation level.

OFF

READ_COMMITTED_SNAPSHOT

When ON is specified, transactions specifying the READ COMMITTED isolation level use row versioning instead of locking. When a transaction runs at the READ COMMITTED isolation level, all statements see a snapshot of data as it exists at the start of the statement.

When OFF is specified, transactions that specify the READ COMMITTED isolation level use locking.

When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE finishes. The database does not have to be in single-user mode.

OFF

SQL Options

Control ANSI-compliance options.

Option

Description

Default value

ANSI_NULL_DEFAULT

Determines the default value, NULL or NOT NULL, of a column, alias data type, or CLR user-defined type for which the nullability is not explicitly defined in CREATE TABLE or ALTER TABLE statements.

When ON is specified, the default value is NULL.

When OFF is specified, the default value is NOT NULL.

OFF

ANSI_NULLS

When ON is specified, all comparisons to a null value evaluate to UNKNOWN.

When OFF is specified, comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

OFF

ANSI_PADDING

When set to ON, trailing blanks in character values inserted into varchar or nvarchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column.

When set to OFF, the trailing blanks (for varchar or nvarchar) and zeros (for varbinary) are trimmed. This setting affects only the definition of new columns.

char and binary columns that allow nulls are padded to the length of the column when ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when ANSI_PADDING is OFF. char and binary columns that do not allow nulls are always padded to the length of the column.

OFF

ANSI_WARNINGS

When ON is specified, errors or warnings are issued when conditions such as divide-by-zero occur or null values appear in aggregate functions.

When OFF is specified, no warnings are raised and null values are returned when conditions such as divide-by-zero occur.

OFF

ARITHABORT

When ON is specified, a query is ended when an overflow or divide-by-zero error occurs during query execution.

When OFF is specified, a warning message is displayed when one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred.

OFF

CONCAT_NULL_YIELDS_NULL

When ON is specified, the result of a concatenation operation is NULL when either operand is NULL.

When OFF is specified, the null value is treated as an empty character string.

OFF

QUOTED_IDENTIFIER

When ON is specified, double quotation marks can be used to enclose delimited identifiers.

When OFF is specified, identifiers cannot be in quotation marks and must follow all Transact-SQL rules for identifiers.

OFF

NUMERIC_ROUNDABORT

When ON is specified, an error is generated when loss of precision occurs in an expression.

When OFF is specified, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

OFF

RECURSIVE_TRIGGERS

When ON is specified, recursive firing of AFTER triggers is allowed.

When OFF is specified, only direct recursive firing of AFTER triggers is not allowed.

OFF

To change database options