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 


Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net