Setting Database Options

In this section, I will concentrate on how to set the various database options, as opposed to how the settings will affect your database. Many of the settings are fairly intuitively named, which helps, and are covered in detail elsewhere in this book. For example, a detailed explanation of the Recovery options can be found in Chapter 16, "Database Backup and Restore."

The Database Options

SQL Server has five categories of options that can be set to control database behavior. The categories and the associated values are listed in Table 11.1.

Table 11.1. Database Options

Option Category	Option
Auto Options	AUTO_CLOSE {ON OFF}
	AUTO_CREATE_STATISTICS {ON OFF}
	AUTO_UPDATE_STATISTICS {ON OFF}
	AUTO_SHRINK {ON OFF}
Cursor Options	CURSOR_CLOSE_ON_COMMIT {ON OFF}
	CURSOR_DEFAULT {LOCAL GLOBAL}
Recovery Options	RECOVERY {FULL BULK_LOGGED SIMPLE}
	TORN_PAGE_DETECTION {ON OFF}
State Options	SINGLE_USER RESTRICTED_USER MULTI_USER
	OFFLINE ONLINE
	READ_ONLY READ_WRITE
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}

Using Enterprise Manager to Set Database Options

Many of the database options can be set right from the Options tab of the Database Properties page in Enterprise Manager, as shown in Figure 11.8.

Figure 11.8. Setting database options in Enterprise Manager.

The OFFLINE ONLINE state option is also accessible in Enterprise Manager by right-clicking a database and selecting the All Tasks menu; OFFLINE or ONLINE appears, depending on the current state of the database.

Using T-SQL to Set Database Options

If you prefer to use T-SQL, or if the option you need to set doesn't appear in Enterprise Manager, you can use the ALTER DATABASE command to set options as well. The following example sets AUTO_SHRINK to OFF for the Credit database:

 ALTER DATABASE Credit     SET AUTO_SHRINK OFF 

The system-stored procedure sp_dboption is still available to check and set database options, but Microsoft advises it is available only for backward compatibility, and that you should use ALTER DATABASE instead. If you want to be a rebel and use it anyway, here is the syntax:

 sp_dboption 'credit', 'autoshrink', 'false' 

An interesting note is that if you use Enterprise Manager to generate a database creation script from an existing database, it uses sp_dboption to set the database options. I guess somebody missed the "We advise against using this" memo.

Retrieving Option Information

The settings for database options are best retrieved using sp_helpdb or the DATABASEPROPERTYEX function. The sp_dboption stored procedure can be used to display option information as well, but remember: It's only around for backward compatibility.

The syntax for sp_helpdb is as follows :

 sp_helpdb databasename 

The database options are listed, comma delimited, in the status column of the return. All Boolean options that are set to ON are returned, and all non-Boolean values are returned with the value to which they are set. Because the status column is a varchar (340), it can be difficult to view the option you are interested in. A more precise way of displaying the option information is with the DATABASEPROPERTYEX function. The function accepts input values for the database name and the property (options are properties), as shown here:

 SELECT DATABASEPROPERTYEX ('Credit', 'IsAutoShrink') 

This function returns a value of 1 or 0 for Boolean values ”with 1 being "on" or "true" ”and returns the actual value for non-Booleans. Table 11.2 lists the valid properties.

Table 11.2. Valid Properties for the DATABASEPROPERTYEX Function

Property	Description	Value Returned
Collation	Default collation name.	Collation name
IsAnsiNullDefault	Database follows SQL-92 rules for null values.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAnsiNullsEnabled	All comparisons to a null evaluate to unknown.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAnsiPaddingEnabled	Strings are padded to the same length before comparison or insert.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAnsiWarningsEnabled	Error or warning messages are issued on standard error conditions.	1 = TRUE 0 = FALSE NULL = Invalid input
IsArithmeticAbortEnabled	Queries are terminated by overflow or divide-by-zero errors.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoClose	Database shuts down after the last user exits.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoCreateStatistics	Existing statistics are automatically updated.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoShrink	Database files are set to automatically shrink.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoUpdateStatistics	Auto update statistics are enabled.	1 = TRUE 0 = FALSE NULL = Invalid input
IsCloseCursorsOnCommit-	Open cursors are closed when a transaction is committed.	1 = TRUE Enabled 0 = FALSE NULL = Invalid input
IsFulltextEnabled	Database is full-text enabled.	1 = TRUE 0 = FALSE NULL = Invalid input
IsInStandBy	Database is in Standby mode.	1 = TRUE 0 = FALSE NULL = Invalid input
IsLocalCursorsDefault	Cursor declarations default to LOCAL .	1 = TRUE 0 = FALSE NULL = Invalid input
IsMergePublished	The tables of a database can be published for replication.	1 = TRUE 0 = FALSE NULL = Invalid input
IsNullConcat	Null concatenation yields NULL .	1 = TRUE 0 = FALSE NULL = Invalid input
IsNumericRoundAbortEnabled	Loss of precision in expressions generates errors.	1 = TRUE 0 = FALSE NULL = Invalid input
IsQuotedIdentifiersEnabled	Double quotation marks can be used as identifiers.	1 = TRUE 0 = FALSE NULL = Invalid input
IsRecursiveTriggersEnabled	Recursive triggers are enabled.	1 = TRUE 0 = FALSE NULL = Invalid input
IsSubscribed	Database can be subscribed for publication.	1 = TRUE 0 = FALSE NULL = Invalid input
IsTornPageDetectionEnabled	SQL Server detects incomplete I/O operations.	1 = TRUE 0 = FALSE NULL = Invalid input
Recovery	Recovery model for the database.	FULL BULK LOGGED SIMPLE
SQLSortOrder	SQL Server sort order ID.	0 = Windows collation >0 = Sort order ID
Status	Database status.	ONLINE OFFLINE RESTORING RECOVERING SUSPECT
Updateability	Indicates whether database is read only.	READ_ONLY READ_WRITE
UserAccess	Indicates user access mode.	SINGLE_USER RESTRICTED_ USER MULTI_USER
Version	Internal version number of SQL Server.	Version number = Database is open NULL = Database is closed