Setting Database Options

A number of database-level options that determine the characteristics of the database can be set for each database. Only the system administrator, database owner, members of the sysadmin and dbcreator fixed server roles and db_owner fixed database roles can modify these options. These options are unique to each database and do not affect other databases. The database options can be set by using the SET clause of the ALTER DATABASE statement, the sp_dboption system stored procedure or, in some cases, SQL Server Enterprise Manager.

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

After you set a database option, a checkpoint is automatically issued that causes the modification to take effect immediately.

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

There are five categories of database options:

• Auto options
  
  
• Cursor options
  
  
• Recovery options
  
  
• SQL options
  
  
• State options

Auto Options

Auto options control certain automatic behaviors.

AUTO_CLOSE

When set to ON, the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete, thereby freeing any resources. By default, this option is set to ON for all databases when using Microsoft® SQL Server™ 2000 Desktop Engine, and OFF for all other editions, regardless of operating system. The database reopens automatically when a user tries to use the database again. If the database was shut down cleanly, the database is not reopened until a user tries to use the database the next time SQL Server is restarted. When set to OFF, the database remains open even if no users are currently using the database.

The AUTO_CLOSE option is useful for desktop databases because it allows database files to be managed as normal files. They can be moved, copied to make backups, or even e-mailed to other users. The AUTO_CLOSE option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will impair performance.

The status of this option can be determined by examining the IsAutoClose property of the DATABASEPROPERTYEX function.

AUTO_CREATE_STATISTICS

When set to ON, statistics are automatically created on columns used in a predicate. Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query. If the statistics are not used, SQL Server automatically deletes them. When set to OFF, statistics are not automatically created by SQL Server; instead, statistics can be manually created. For more information, see Statistical Information.

By default, AUTO_CREATE_STATISTICS is ON.

The status of this option can be determined by examining the IsAutoCreateStatistics property of the DATABASEPROPERTYEX function.

AUTO_UPDATE_STATISTICS

When set to ON, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed. When set to OFF, existing statistics are not automatically updated; instead, statistics can be manually updated. For more information, see Statistical Information.

By default, AUTO_UPDATE_STATISTICS is set to ON.

The status of this option can be determined by examining the IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.

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. When set to OFF, the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to ON for all databases when using SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system.

AUTO_SHRINK only reduces the size of the transaction log if the database is set to SIMPLE recovery model or if the log is backed up.

The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is greater.

It is not possible to shrink a read-only database.

The status of this option can be determined by examining the IsAutoShrink property of the DATABASEPROPERTYEX function.

Cursor Options

Cursor options control cursor behavior and scope.

CURSOR_CLOSE_ON_COMMIT

When set to ON, any open cursors are closed automatically (in compliance with SQL-92) when a transaction is committed. By default, this setting is OFF and cursors remain open across transaction boundaries, closing only when the connection is closed or when they are explicitly closed.

Connection-level settings (set using the SET statement) override the default database setting for CURSOR_CLOSE_ON_COMMIT. By default, ODBC and OLE DB clients issue a connection-level SET statement setting CURSOR_CLOSE_ON_COMMIT to OFF for the session when connecting to SQL Server. For more information, see SET CURSOR_CLOSE_ON_COMMIT.

The status of this option can be determined by examining the IsCloseCursorsOnCommitEnabled property of the DATABASEPROPERTYEX function.

CURSOR_DEFAULT LOCAL | GLOBAL

When CURSOR_DEFAULT LOCAL is set, and a cursor is not defined as GLOBAL when it is 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. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless it was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

When CURSOR_DEFAULT GLOBAL is set, 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. The cursor is implicitly deallocated only at disconnect. CURSOR_DEFAULT GLOBAL is the default setting. For more information, see DECLARE CURSOR.

The status of this option can be determined by examining the IsLocalCursorsDefault property of the DATABASEPROPERTYEX function.

Recovery Options

Recovery options controls the recovery model for the database.

RECOVERY FULL | BULK_LOGGED | SIMPLE

When FULL is specified, database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged. For more information, see Full Recovery.

When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery. For more information, see Bulk-Logged Recovery.

When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup. For more information, see Simple Recovery.

SIMPLE is the default setting for SQL Server Desktop Edition and the data engine, and FULL is the default for all other editions.

The status of this option can be determined by examining the Recovery property of the DATABASEPROPERTYEX function.

TORN_PAGE_DETECTION

This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.

When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte (KB) database page when the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the page was written incorrectly; a torn page is detected. Torn pages are usually detected during recovery because any page that was written incorrectly is likely to be read by recovery.

Although SQL Server database pages are 8 KB, disks perform I/O operations using a 512-byte sector. Therefore, 16 sectors are written per database page. A torn page can occur if the system fails (for example, due to power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. If the first sector of a database page is successfully written before the failure, the database page on disk will appear as updated, although it may not have succeeded.

Note  Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all.

If a torn page is detected, an I/O error is raised and the connection is killed. If the torn page is detected during recovery, the database is also marked suspect. The database backup should be restored, and any transaction log backups applied, because it is physically inconsistent. 

By default, TORN_PAGE_DETECTION is ON.

The current setting of this option can be determined by examining the IsTornPageDetectionEnabled property of DATABASEPROPERTYEX.

SQL Options

SQL options control ANSI compliance options.

ANSI_NULL_DEFAULT

Allows the user to control the database default nullability. When NULL or NOT NULL is not specified explicitly, a user-defined data type or a column definition uses the default setting for nullability. Nullability is determined by session and database settings. Microsoft SQL Server™2000 defaults to NOT NULL. For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL.

When this option is set to ON, all user-defined data types or columns that are not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement default to allowing null values. Columns that are defined with constraints follow constraint rules regardless of this setting.

Connection-level settings (set using the SET statement) override the default database-level setting for ANSI_NULL_DEFAULT. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session when connecting to SQL Server. For more information, see SET ANSI_NULL_DFLT_ON.

The status of this option can be determined by examining the IsAnsiNullDefault property of the DATABASEPROPERTYEX function.

ANSI_NULLS

When set to ON, all comparisons to a null value evaluate to NULL (unknown). When set to OFF, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL. By default, the ANSI_NULLS database option is OFF.

Connection-level settings (set using the SET statement) override the default database setting for ANSI_NULLS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to SQL Server. For more information, see SET ANSI_NULLS.

SET ANSI_NULLS also must be set to ON when you create or manipulate indexes on computed columns or indexed views.

The status of this option can be determined by examining the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.

ANSI_PADDING

When set to ON, trailing blanks in character values inserted into varchar 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) and zeros (for varbinary) are trimmed. This setting affects only the definition of new columns.

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

Important  It is recommended that ANSI_PADDING always be set to ON. SET ANSI_PADDING must be ON when creating or manipulating indexes on computed columns or indexed views.

The status of this option can be determined by examining the IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function.

ANSI_WARNINGS

When set to ON, errors or warnings are issued when conditions such as "divide by zero" occur or null values appear in aggregate functions. When set to OFF, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as "divide by zero" occur. By default, ANSI_WARNINGS is OFF.

SET ANSI_WARNINGS must be set to ON when you create or manipulate indexes on computed columns or indexed views.

Connection-level settings (set using the SET statement) override the default database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session when connecting to SQL Server. For more information, see SET ANSI_WARNINGS.

The status of this option can be determined by examining the IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function.

ARITHABORT

When set to ON, an overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. When set to OFF, a warning message is displayed if one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred.

SET ARITHABORT must be set to ON when you create or manipulate indexes on computed columns or indexed views

The status of this option can be determined by examining the IsArithmeticAbortEnabled property of the DATABASEPROPERTYEX function.

NUMERIC_ROUNDABORT

If set to ON, an error is generated when loss of precision occurs in an expression. When set to OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

SET NUMERIC_ROUNDABORT must be set to OFF when you create or manipulate indexes on computed columns or indexed views.

The status of this option can be determined by examining the IsNumericRoundAbortEnabled property of the DATABASEPROPERTYEX function.

CONCAT_NULL_YIELDS_NULL

When set to ON, if one of the operands in a concatenation operation is NULL, the result of the operation is NULL. For example, concatenating the character string "This is" and NULL results in the value NULL, rather than the value "This is".

When set to OFF, concatenating a null value with a character string yields the character string as the result; the null value is treated as an empty character string. By default, CONCAT_NULL_YIELDS_NULL is OFF.

SET CONCAT_NULL_YIELDS_NULL must be set to ON when you create or manipulate indexes on computed columns or indexed views.

Connection-level settings (set using the SET statement) override the default database setting for CONCAT_NULL_YIELDS_NULL. By default, ODBC and OLE DB clients issue a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session when connecting to SQL Server. For more information, see SET CONCAT_NULL_YIELDS_NULL.

The status of this option can be determined by examining the IsNullConcat property of the DATABASEPROPERTYEX function.

QUOTED_IDENTIFIER

When set to ON, identifiers can be delimited by double quotation marks and literals must be delimited by single quotation marks. All strings delimited by double quotation marks are interpreted as object identifiers. Quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be keywords and can include characters not generally allowed in Transact-SQL identifiers. If a single quotation mark (') is part of the literal string, it can be represented by double quotation marks (").

When set to OFF (default), identifiers cannot be in quotation marks and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

SQL Server also allows identifiers to be delimited by square brackets ([ ]). Bracketed identifiers can always be used, regardless of the setting of QUOTED_IDENTIFIER. For more information, see Delimited Identifiers.

SET QUOTED_IDENTIFIER must be set to ON when you create or manipulate indexes on computed columns or indexed views.

Connection-level settings (set using the SET statement) override the default database setting for QUOTED_IDENTIFIER. By default, ODBC and OLE DB clients issue a connection-level SET statement setting QUOTED_IDENTIFIER to ON when connecting to SQL Server. For more information, see SET QUOTED_IDENTIFIER.

The status of this option can be determined by examining the IsQuotedIdentifiersEnabled property of the DATABASEPROPERTYEX function.

RECURSIVE_TRIGGERS

When set to ON, triggers are allowed to fire recursively. When set to OFF (default), triggers cannot be fired recursively.

Note  Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF. To disable indirect recursion, you must also set the nested triggers server option to 0.

The status of this option can be determined by examining the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.

State Options

State options control whether the database is online or offline, who can connect to the database, and whether the database is in read-only mode. A termination clause can be used to control how connections are terminated when the database is transitioned from one state to another.

OFFLINE | ONLINE

When OFFLINE is specified, the database is closed and shutdown cleanly and marked offline. The database cannot be modified while the database is offline.

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

The status of this option can be determined by examining the Status property of the DATABASEPROPERTYEX function.

READ_ONLY | READ_WRITE

When READ_ONLY is specified, the database is in read-only mode. Users can retrieve data from the database, but cannot modify the data. Because a read-only database does not allow data modifications:

• Automatic recovery is skipped at system startup.
  
  
• Shrinking the database is not possible.
  
  
• No locking takes place in read-only databases, which can result in faster query performance.

When READ_WRITE is specified, users can retrieve and modify data. READ_WRITE is the default setting.

The status of this option can be determined by examining the Updateability property of the DATABASEPROPERTYEX function.

SINGLE_USER | RESTRICTED_USER | MULTI_USER

SINGLE_USER allows one user at a time to connect to the database. All other user connections are broken. The timeframe for breaking the connection is controlled by the termination clause of the ALTER DATABASE statement. New connection attempts are refused. The database remains in SINGLE_USER mode even if the user who set the option logs off. At that point, a different user (but only one) can connect to the database.

To allow multiple connections, the database must be changed to RESTRICTED_USER or MULTI_USER mode.

RESTRICTED_USER allows only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but it does not limit their number. Users who are not members of these roles are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. Moreover, new connection attempts by unqualified users are refused.

MULTI_USER allows all users with the appropriate permissions to connect to the database. MULTI_USER is the default setting.

The status of this option can be determined by examining the UserAccess property of the DATABASEPROPERTYEX function.

WITH <termination>

The termination clause of the ALTER DATABASE statement specifies how to terminate incomplete transactions when the database is to be transitioned from one state to another. Transactions are terminated by breaking their connections to the database. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely, until the transactions commit or roll back on their own.

ROLLBACK AFTER integer [SECONDS]

ROLLBACK AFTER integer SECONDS waits for the specified number of seconds and then breaks unqualified connections. Incomplete transactions are rolled back. When the transition is to SINGLE_USER mode, unqualified connections are all connections except the one issuing the ALTER DATABASE statement. When the transition is to RESTRICTED_USER mode, unqualified connections are connections for users who are not members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles.

ROLLBACK IMMEDIATE

ROLLBACK IMMEDIATE breaks unqualified connections immediately. All incomplete transactions are rolled back. Unqualified connections are the same as those described for ROLLBACK AFTER integer SECONDS.

NO_WAIT

NO_WAIT checks for connections before attempting to change the database state and causes the ALTER DATABASE statement to fail if certain connections exist. When the transition is to SINGLE_USER mode, the ALTER DATABASE statement fails if any other connections exist. When the transition is to RESTRICTED_USER mode, the ALTER DATABASE statement fails if any unqualified connections exist.

To change database options

Transact-SQL