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.
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).
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).
Control certain automatic behaviors.
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.
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.
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.
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.
When True, updates the statistics asynchronously.
Control cursor behavior and scope.
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.
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.
Control whether the database is online or offline, who can connect to the database, and whether the database is in read-only mode.
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.
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.
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.
Control the date_correlation_optimization option.
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.
For more information, see Optimizing Queries That Access Correlated datetime Columns.
Control whether the database can be accessed by external resources such as objects from another database.
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.
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.
Control the recovery model for the database.
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.
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.
Control Service Broker options.
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.
Determine the transaction isolation level.
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.
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.
Control ANSI-compliance options.
When ON is specified, the default value is NULL.
When OFF is specified, the default value is NOT NULL.
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.
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.
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.
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.
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.
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.
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.
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.
To change database options