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.

graphics/11fig08.jpg

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



    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features
     
    ASPTreeView.com
     
    Evaluation has Т№КЩјexpired.
    Info...