| 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 | |