Setting Database Options

Twenty options can be set for a database to control certain behavior within that database. Most of the options must be set to ON or OFF. By default, all the options that allow only these two values have an initial value of OFF unless they were set to ON in the model database. All databases created after an option is changed in model will have the same values as model. You can easily change the value of some of these options by using SQL Server Enterprise Manager. You can set all of them directly by using the ALTER DATABASE command. You can also use the sp_dboption system stored procedure to set some, but that procedure is provided for backward compatibility only. All the options correspond to bits in the status and status2 columns of sysdatabases, although those bits can also show states that the database owner can't set directly (such as when the database is in the process of being recovered).

Executing the sp_helpdb stored procedure for a database shows you all the values for the non-Boolean options. For the Boolean options, the procedure lists the options that are ON. The option values are all listed in the status column of the output. In addition, the status column of the sp_helpdb output provides the database collation and sort order. The procedure also returns other useful information, such as database size, creation date, and database owner. Executing sp_helpdb with no parameters shows information about all the databases in that installation. The following databases exist on a new default installation of SQL Server, and sp_helpdb produces this output (although the created dates and sizes can vary):

 > EXEC sp_helpdb name db_size owner dbid created status --------- -------- ----- ---- ----------- --------------------------------- master 11.94 MB sa 1 Jul 31 2000 Status=ONLINE, Updateability=R... model 1.13 MB sa 3 Jul 31 2000 Status=ONLINE, Updateability=R... msdb 13.00 MB sa 4 Jul 31 2000 Status=ONLINE, Updateability=R... Northwind 3.63 MB sa 6 Jul 31 2000 Status=ONLINE, Updateability=R... pubs 2.00 MB sa 5 Jul 31 2000 Status=ONLINE, Updateability=R... tempdb 8.50 MB sa 2 Aug 21 2000 Status=ONLINE, Updateability=R... 

The database options are divided into five categories: state options, cursor options, auto option, SQL options, and recovery options. Some of the options, in particular the SQL options, have corresponding SET options that you can turn on or off for a particular connection. Be aware that the ODBC or OLE DB drivers turn a number of these SET options on by default, so applications will act as though the corresponding database option has already been set. (Chapter 6 goes into more detail about the SET options that are set by the ODBC and OLE DB drivers and the interaction with the database options.)

Here is a list of all 20 options, by category. Options listed on a single line are mutually exclusive.

• State options

  SINGLE_USER | RESTRICTED_USER | MULTI_USER
  OFFLINE | ONLINE
  READ_ONLY | READ_WRITE

• Cursor options

  CURSOR_CLOSE_ON_COMMIT { ON | OFF }
  CURSOR_DEFAULT { LOCAL | GLOBAL }

• Auto options

  AUTO_CLOSE {ON | OFF }
  AUTO_CREATE_STATISTICS {ON | OFF }
  AUTO_SHRINK {ON | OFF }
  AUTO_UPDATE_STATISTICS {ON | OFF }

• SQL options

  ANSI_NULL_DEFAULT { ON | OFF }
  ANSI_NULLS { ON | OFF }
  ANSI_PADDING { ON | OFF }
  ANSI_WARNINGS { ON | OFF }
  ARITHABORT { ON | OFF }
  CONCAT_NULL_YIELDS_NULL { ON | OFF }
  NUMERIC_ROUNDABORT { ON | OFF }
  QUOTED_IDENTIFIER { ON | OFF }
  RECURSIVE_TRIGGERS { ON | OFF }

• Recovery options

  RECOVERY { FULL | BULK_LOGGED | SIMPLE }
  TORN_PAGE_DETECTION { ON | OFF }

The following sections describe all the options except RECOVERY, which I'll discuss in detail later, in the section titled "Backing Up and Restoring a Database."

State Options

The state options control the usability of the database, in terms of who can use the database and for what operations. There are three aspects to usability: the user access state option determines which users can use the database, the status state option determines whether the database is available to anybody for use, and the updateability state option determines what operations can be performed on the database. You control each of these aspects by using the ALTER DATABASE command to enable an option for the database. None of the state options uses the keywords ON and OFF to control the state value.

SINGLE_USER | RESTRICTED_USER | MULTI_USER

These three options describe the user access property of a database. They are mutually exclusive; setting any one of them unsets the others. To set one of these options for your database, you just use the option name. For example, to set the pubs database to single user mode, you use the following code:

 ALTER DATABASE pubs SINGLE_USER 

A database in SINGLE_USER mode can have only one connection at a time. A database in RESTRICTED_USER mode can have connections only from users who are considered "qualified." A qualified user is any member of the dbcreator or sysadmin server roles or any member of the db_owner role for that database. The default for a database is MULTI_USER mode, which means anyone with a valid user name in the database can connect to it. If you attempt to change a database's state to a mode that is incompatible with the current conditions—for example, if you try to change the database to SINGLE_USER mode when other connections exist—SQL Server's behavior is determined by the TERMINATION option you specify. I'll discuss termination options shortly.

To determine which user access value is set for a database, you can use the DATABASEPROPERTYEX() function, as shown here:

 SELECT DATABASEPROPERTYEX('<name of database>', 'UserAccess') 

In previous versions of SQL Server, database access was controlled using the procedure sp_dboption and setting the value of the options dbo use only or single user. If both of these options had a value of false, the database was in MULTI_USER mode. If the option single user was set to true, the database was in SINGLE_USER mode, and if the option dbo use only was set to true and single user was set to false, the database was in a state similar to RESTRICTED_USER. In SQL Server 2000, the user access value is still determined by the same bits in the sysdatabases.status field that were used in previous versions to determine single user or dbo use only mode. If the 12 bit is set (with a value of 4096), the database is in SINGLE_USER mode, and if the 11 bit is set (with a value of 2048), the database is in RESTRICTED_USER mode. In SQL Server 2000, you cannot have both of these bits set at the same time.

OFFLINE | ONLINE

These two options are the status options; the status property of a database is described using one of these option names. They are mutually exclusive. The default is for a database to be ONLINE. As with the user access options, when you use ALTER DATABASE to put the database in one of these modes, you don't specify a value of ON or OFF—you just use the name of the option. When a database is set to OFFLINE, the database is closed and shut down cleanly and marked as off line. The database cannot be modified while the database is off line. A database cannot be put into OFFLINE mode if there are any connections in the database. Whether SQL Server waits for the other connections to terminate or generates an error message is determined by the TERMINATION option specified.

The following code examples show how to set a database's status value to OFFLINE and how to determine the status of a database:

 ALTER DATABASE pubs SET OFFLINE SELECT DATABASEPROPERTYEX('pubs', 'status') 

DATABASEPROPERTYEX() could return status values other than OFFLINE and ONLINE, but those values are not directly settable using ALTER DATABASE. A database can have the status value RESTORING while it is in the process of being restored from a backup. It can have the status value RECOVERING during a restart of SQL Server. The restore process is done on one database at a time, and until SQL Server has finished restoring a database, the database has a status of RECOVERING. It can have a status of SUSPECT if the recovery process could not be completed for some reason—the most common ones being that one or more of the log files for the database is unavailable or unreadable. Each of the five status values corresponds to a different bit set in the sysdatabases.status column.

READ_ONLY | READ_WRITE

These two options are the updateability options; they describe the updateability property of a database. They are mutually exclusive. The default is for a database to be READ_WRITE. As with the user access options, when you use ALTER DATABASE to put the database in one of these modes, you don't specify a value of ON or OFF, you just use name of the option. When the database is in READ_WRITE mode, any user with the appropriate permissions can carry out data modification operations. In READ_ONLY mode, no INSERT, UPDATE, or DELETE operations can be executed. In addition, because no modifications are done when a database is in READ_ONLY mode, automatic recovery is not run on this database when SQL Server is restarted and no locks need to be acquired during any SELECT operations. Shrinking a database in READ_ONLY mode is not possible.

A database cannot be put into READ_ONLY mode if there are any connections to the database. Whether SQL Server waits for the other connections to terminate or generates an error message is determined by the TERMINATION option specified.

The following code examples show how to set a database's updatability value to READ_ONLY and how to determine the updatability of a database:

 ALTER DATABASE pubs SET READ_ONLY SELECT DATABASEPROPERTYEX('pubs', 'updateability') 

Termination options

As I just mentioned, several of the state options cannot be set when a database is in use or when it is in use by an unqualified user. You can specify how SQL Server should handle this situation by indicating a termination option in the ALTER DATABASE command. You can specify that SQL Server wait for the situation to change, that it generate an error message, or that it terminate the connections of nonqualified users. The termination option determines SQL Server's behavior in the following situations:

• When you attempt to change a database to SINGLE_USER and it has more than one current connection
• When you attempt to change a database to RESTRICTED_USER and unqualified users are currently connected to it
• When you attempt to change a database to OFFLINE and there are current connections to it
• When you attempt to change a database to READ_ONLY and there are current connections to it

SQL Server's default behavior in any of these situations is to wait indefinitely. The following TERMINATION options change this behavior:

ROLLBACK AFTER integer [SECONDS] This option causes SQL Server to wait for the specified number of seconds and then break 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 of users who are not members of the db_owner fixed database role or the dbcreator and sysadmin fixed server roles.

ROLLBACK IMMEDIATE This option breaks unqualified connections immediately. All incomplete transactions are rolled back. Unqualified connections are the same as those described earlier.

NO_WAIT This option causes SQL Server to check for connections before attempting to change the database state and causes the ALTER DATABASE statement to fail if certain connections exist. If the database is being set to SINGLE_USER mode, the ALTER DATABASE statement fails if any other connections exist. If the transition is to RESTRICTED_USER mode, the ALTER DATABASE statement fails if any unqualified connections exist.

The following command changes the user access option of the pubs database to SINGLE_USER and generates an error if any other connections to the pubs database exist:

 ALTER DATABASE pubs SET SINGLE_USER WITH NO_WAIT 

Cursor Options

All of the cursor options control the behavior of server-side cursors that were defined using one of the following Transact-SQL commands for defining and manipulating cursors: DECLARE, OPEN, FETCH, CLOSE, and DEALLOCATE. In Chapter 13, I'll discuss Transact-SQL cursors in detail.

CURSOR_CLOSE_ON_COMMIT {ON | OFF}

When this option is set to ON, any open cursors are automatically closed (in compliance with SQL-92) when a transaction is committed or rolled back. If OFF (the default) is specified, cursors remain open after a transaction is committed. Rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

CURSOR_DEFAULT {LOCAL | GLOBAL}

When this option is set to LOCAL and cursors aren't specified as GLOBAL when created, the scope of any cursor is local to the batch, stored procedure, or trigger in which it 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 by a stored procedure output parameter. When this option is set to GLOBAL and cursors aren't specified 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.

Auto Options

The auto options affect actions that SQL Server might take automatically. All these options are Boolean options, with values of either ON or OFF.

AUTO_CLOSE

When this option is set to ON (the default when SQL Server runs on Windows 98), the database is closed and shut down cleanly when the last user of the database exits, thereby freeing any resources. When a user tries to use the database again, it automatically reopens. If the database was shut down cleanly, the database isn't initialized (reopened) until a user tries to use the database the next time SQL Server is restarted. The AUTO_CLOSE option is handy for personal SQL Server databases because it allows you to manage database files as normal files. You can move them, copy them to make backups, or even e-mail them to other users. However, you shouldn't use this option 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 hurt performance.

AUTO_CREATE_STATISTICS

When this option is set to ON (the default), statistics are automatically created by the SQL Server query optimizer on columns referenced in a query's WHERE clause. Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query.

AUTO_SHRINK

When this option is set to ON, all of a database's files are candidates for periodic shrinking. Both data files and log files can be automatically shrunk by SQL Server. The only way to free space in the log files so that they can be shrunk is to back up the transaction log or set the recovery mode to SIMPLE. The log files shrink at the point that the log is backed up or truncated.

AUTO_UPDATE_STATISTICS

When this option is set to ON (the default), existing statistics are automatically updated if the data in the tables has changed. SQL Server keeps a counter of the modifications that have been made to a table and uses it to determine when statistics are outdated. When this option is set to OFF, existing statistics are not automatically updated. (They can be updated manually.) I'll discuss statistics in much more detail in Chapter 15.

SQL Options

The SQL options control how various SQL statements are interpreted. All of these are Boolean options. Although the default for all of these options is OFF for SQL Server itself, many tools, such as the SQL Query Analyzer, and many programming interfaces, such as ODBC, enable certain session-level options that override the database options and make it appear as if the ON behavior is the default. I'll discuss the interaction of the SET options with the database options in Chapter 7.

ANSI_NULL_DEFAULT

When this option is set to ON, columns comply with the ANSI-92 rules for column nullability. That is, if you don't specifically indicate whether a column in a table allows NULL values, NULLs are allowed. When this option is set to OFF, newly created columns do not allow NULLs if no nullability constraint is specified.

ANSI_NULLS

When this option is set to ON, any comparisons with a NULL value result in UNKNOWN, as specified by the ANSI-92 standard. If this option is set to OFF, comparisons of non-Unicode values to NULL result in a value of TRUE if both values being compared are NULL.

ANSI_PADDING

When this option is set to ON, strings being compared to each other are set to the same length before the comparison takes place. When this option is OFF, no padding takes place.

ANSI_WARNINGS

When this option is set to ON, errors or warnings are issued when conditions such as division by zero or arithmetic overflow occur.

ARITHABORT

When this option is set to ON, a query is terminated when an arithmetic overflow or division-by-zero error is encountered during the execution of a query. When this option is OFF, the query returns NULL as the result of the operation.

CONCAT_NULL_YIELDS_NULL

When this option is set to ON, concatenating two strings results in a NULL string if either of the strings is NULL. When this option is set to OFF, a NULL string is treated as an empty (zero-length) string for the purposes of concatenation.

NUMERIC_ROUNDABORT

When this option is set to ON, an error is generated if an expression will result in loss of precision. When this option is OFF, the result is simply rounded. The setting of ARITHABORT determines the severity of the error. If ARITHABORT is OFF, only a warning is issued and the expression returns a NULL. If ARITHABORT is ON, an error is generated and no result is returned.

QUOTED_IDENTIFIER

When this option is set to ON, identifiers such as table and column names can be delimited by double quotation marks and literals must then be delimited by single quotation marks. All strings delimited by double quotation marks are interpreted as object identifiers. Quoted identifiers don't have to follow the Transact-SQL rules for identifiers when QUOTED_IDENTIFIER is ON. They can be keywords and can include characters not normally allowed in Transact-SQL identifiers, such as spaces and dashes. You can't use double quotation marks to delimit literal string expressions; you must use single quotation marks. If a single quotation mark is part of the literal string, it can be represented by two single quotation marks ("). This option must be set to ON if reserved keywords are used for object names in the database. When it is OFF, identifiers can't be in quotation marks and must follow all Transact-SQL rules for identifiers.

RECURSIVE_TRIGGERS

When this option is set to ON, triggers can fire recursively, either directly or indirectly. Indirect recursion occurs when a trigger fires and performs an action that causes a trigger on another table to fire, thereby causing an update to occur on the original table, which causes the original trigger to fire again. For example, an application updates table T1, which causes trigger Trig1 to fire. Trig1 updates table T2, which causes trigger Trig2 to fire. Trig2 in turn updates table T1, which causes Trig1 to fire again. Direct recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates table T3, which causes trigger Trig3 to fire. Trig3 updates table T3 again, which causes trigger Trig3 to fire again. When this option is OFF (the default), triggers can't be fired recursively.

Recovery Options

Recovery options control how much recovery can be done on a SQL Server database. The RECOVERY option itself also controls how much information is logged and how much of the log is available for backups. I'll cover this option in more detail in the section titled "Backing Up and Restoring a Database" later in this chapter.

TORN_PAGE_DETECTION

When this option is set to ON (the default), it causes a bit to be flipped for each 512byte sector in a database page (8 KB) whenever the page is written to disk. It allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages. 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 has been detected.) Although SQL Server database pages are 8 KB, disks perform I/O operations using 512-byte sectors. Therefore, 16 sectors are written per database page. A torn page can occur if the system crashes (for example, because of 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 crash, it will appear that the database page on disk was updated even though the operation might not have succeeded. Using battery-backed disk caches can ensure that the data is successfully written to disk or not written at all. In this case, you can set TORN_PAGE_DETECTION to OFF because it isn't needed. If a torn page is detected, the database must be restored from backup because it will be physically inconsistent.