Database Configuration Options

Database options are set to allow for application or procedural requirements and to provide for administrative configuration. You will interact with these settings to set up backups, allow for specific procedures, and provide appropriate access levels, depending on what is needed for a given process. Learn the settings that are required for each process and know the resulting effect on the system under different operating configurations.

Standard database configuration settings are available through the Database Properties in the Enterprise Manager, or they can be accessed using the sp_dboption stored procedure. Some of the more advanced settings cannot be set singly; they must be set in combination with other settings.

There are five categories of database options:

• Auto options AUTO_CLOSE, AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS, and AUTO_SHRINK.

• Cursor options CURSOR_CLOSE_ON_COMMIT and CURSOR_DEFAULT LOCAL or GLOBAL.

• Recovery options RECOVERY FULL or BULK_LOGGED or SIMPLE and TORN_PAGE_DETECTION.

• SQL options ANSI_NULL_DEFAULT, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, NUMERIC_ROUNDABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, and RECURSIVE_TRIGGERS.

• State options OFFLINE or ONLINE, READ_ONLY or READ_WRITE, SINGLE_USER or RESTRICTED_USER or MULTI_USER, and WITH ROLLBACK AFTER or WITH ROLLBACK IMMEDIATE or NO_WAIT.

These options are described in the following sections.

Settings for Automatic Actions

One group of settings tell a database to take actions in an automated fashion. Actions that the server would normally wait for specific instruction to perform can be set to automatically occur based on properties of the environment. These options in most cases allow for better resource usage, but some may also have associated overhead. These options are summarized in the following subsections. The sp_dboption procedure is always executed within the context of the master, as in the following:

 Use Master sp_dboption 'pubs', 'read only', 'TRUE' 

AUTO_CREATE_STATISTICS

When the AUTO_CREATE_STATISTICS option is set to ON, statistics are automatically created on columns used in a predicate. A predicate is an expression that evaluates to trUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, and the join conditions of FROM clauses. Adding statistics improves query performance because the optimizer can better determine how to evaluate queries. If the statistics are not used, SQL Server automatically deletes them. When this option is set to OFF, SQL Server does not automatically create statistics; instead, statistics can be manually created.

AUTO_SHRINK

When the AUTO_SHRINK option is set to ON, the database files are set up for periodic shrinking. Any database-associated file, data, or log can be shrunk automatically. When the option is set to OFF, the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to ON for all databases in SQL Server Desktop Edition, and OFF for all other editions, regardless of the operating system.

AUTO_UPDATE_STATISTICS

When the AUTO_UPDATE_STATISTICS option is set to ON, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed. When this option is set to OFF, existing statistics are not automatically updated; instead, statistics can be manually updated.

AUTO_CLOSE

When the AUTO_CLOSE option is set to ON, server resources are freed up as soon as the database is closed and shut down cleanly when the last user of the database exits. By default, this option is set to ON for all databases in the Desktop Engine, and OFF for all other editions. The database reopens automatically when a user tries to use the database again. When this option is set to OFF, the database remains open even if no users are currently using it.

Arguably the AUTO_CLOSE options is also a cursor option because it will also affect the resources tied up through the use of cursors.

Moving the Cursor

Cursor activities are usually set in motion by an application, but they can utilize resources on the server. Operating as a record selector, cursors locate the current data being acted on and keep track of data manipulations. There are a couple of options that can be set to help control how these resources react, as detailed next.

CURSOR_CLOSE_ON_COMMIT

When CURSOR_CLOSE_ON_COMMIT is set to ON, any open cursors are closed automatically when a transaction using the cursor is committed. By default, this setting is OFF and cursors remain open across transaction boundaries, closing only when the connection is closed or when they are explicitly closed, which is usually when a procedure finishes.

CURSOR_DEFAULT LOCAL or GLOBAL

When CURSOR_DEFAULT LOCAL is set and a cursor is not defined as GLOBAL when it is created, the scope of the cursor is local to the batch, stored procedure, or trigger. The cursor name is valid only within this scope. When CURSOR_DEFAULT GLOBAL is set and a cursor is not defined as LOCAL when it is created, the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch the connection executes.

Settings for Recovery

Aiding in system recovery is important for the next set of options. A little proactive maintenance through the use of these options can save time when you're attempting a full or partial data recovery.

TORN_PAGE_DETECTION

The TORN_PAGE_DETECTION recovery option enables SQL Server to detect incomplete I/O operations caused by power failures or other system outages. When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8KB database page as the page is written to disk. 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 is therefore detected.

RECOVERY FULL or BULK_LOGGED or SIMPLE

When FULL is specified, database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations, such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged. When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery. When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup.

Some of the database properties that were available in previous releases of SQL Server have been combined to form what is referred to in SQL Server 2000 as recovery models. Setting the appropriate model can enable most, if not all, data activity to be recovered in the event of system failures. Three models are supported: Simple, Bulk-Logged, and Full.

The Simple recovery model ensures higher performance during bulk-copy operations and maintains a smaller database log. However, the model does not support transaction log backups and, therefore, there will be data loss in the event of a failure because the database can be restored only to the point of the last full or differential database backup.

Bulk-Logged recovery also allows for high-performance bulk procedures that use minimal log space. Some data might be lost, but because you can perform log backups, usually the only loss will be bulk operations since the last backup.

If recoverability to a specific point in time with as little data loss as possible is the goal, the Full recovery model should be set. The Full recovery model makes the heaviest use of the database logs.

Set Coding Reaction with SQL Options

How the server reacts to a front-end application or any other connection can be configured through the use of several SQL options within the database, is discussed next.

ANSI_NULL_DEFAULT

The ANSI_NULL_DEFAULT option enables the user to control the default nullability. When NULL or NOT NULL is not specified, a user-defined data type or a column definition uses the default setting for nullability. When this option is set to ON, all user-defined data types or columns that are not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement default to allowing null values. Columns that are defined with constraints follow constraint rules regardless of this setting.

ANSI_NULLS

When ANSI_NULLS is set to ON, all comparisons to a null value evaluate to NULL (unknown). When set to OFF, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL. By default, the ANSI_NULLS database option is OFF.

ANSI_PADDING

When ANSI_PADDING is set to ON, trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column. When this option is set to OFF, the trailing blanks and zeros are trimmed. This setting affects only the definition of new columns. It is recommended that ANSI_PADDING always be set to ON. SET ANSI_PADDING must be ON when creating or manipulating indexes on computed columns or indexed views.

ANSI_WARNINGS

When ANSI_WARNINGS is set to ON, errors or warnings are issued when conditions such as divide-by-zero occur or null values appear in aggregate functions. When it's set to OFF, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as divide-by-zero occur. By default, ANSI_WARNINGS is OFF.

ARITHABORT

When ARITHABORT is set to ON, an overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. When this option is set to OFF, a warning message is displayed if one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred.

NUMERIC_ROUNDABORT

If NUMERIC_ROUNDABORT is set to ON, an error is generated when the loss of precision occurs in an expression. When it's set to OFF, losses of precision do not generate error messages, and the result is rounded to the precision of the column or variable storing the result.

CONCAT_NULL_YIELDS_NULL

When CONCAT_NULL_YIELDS_NULL is set to ON, if one of the operands in a concatenation operation is NULL, the result of the operation is NULL. When this option is set to OFF, concatenating a null value with a character string yields the character string as the result.

QUOTED_IDENTIFIER

When QUOTED_IDENTIFIER is set to ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. All strings delimited by double quotation marks are interpreted as object identifiers. Quoted identifiers do not have to follow the T-SQL rules for identifiers. They can be keywords and can include characters not generally allowed in T-SQL identifiers. When this option is set to OFF (the default), identifiers cannot be in quotation marks and must follow all T-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks. Identifiers must be enclosed in square brackets ([ ]) if they contain spaces or other characters or keywords.

RECURSIVE_TRIGGERS

When RECURSIVE_TRIGGERS is set to ON, triggers are enabled to fire recursively. When it's set to OFF (the default), triggers cannot be fired recursively. Just as this set of SQL options controls the interaction with the front end, so does the next set of options. State options, however, control the status of the database or accessibility of the data to connections.

The State of the Server

State options determine whether data is accessible to one or more connections. The database state can be temporarily changed to allow for administration or other specialty tasks.

OFFLINE or ONLINE

When OFFLINE is specified, the database is closed and shut down cleanly and marked offline. The database cannot be modified while it is offline. When ONLINE is specified, the database is open and available for use.

READ_ONLY or READ_WRITE

When READ_ONLY is specified, users can retrieve data from the database but cannot modify the data. Automatic recovery is skipped at system startup and shrinking the database is not possible. No locking takes place in read-only databases, which can result in faster query performance. When READ_WRITE is specified, users can retrieve and modify data.

SINGLE_USER or RESTRICTED_USER or MULTI_USER

SINGLE_USER enables only one user at a time to connect to the database. All other user connections are broken. The timeframe for breaking the connection is controlled by the termination clause of the ALTER DATABASE statement. New connection attempts are refused. RESTRICTED_USER enables only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but it does not limit their number. MULTI_USER enables all users with the appropriate permissions to connect to the database.

WITH termination

The termination clause of the ALTER DATABASE statement specifies how to terminate incomplete transactions. Breaking their connections to the database terminates transactions. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely, until the transactions commit or roll back on their own. ROLLBACK AFTER 'integer' SECONDS waits for the specified number of seconds. ROLLBACK IMMEDIATE breaks unqualified connections immediately. NO_WAIT checks for connections before attempting to change the database state and causes the ALTER DATABASE statement to fail if certain connections exist. When the transition is to SINGLE_USER mode, the ALTER DATABASE statement fails if any other connections exist. When the transition is to RESTRICTED_USER mode, the ALTER DATABASE statement fails if any unqualified connections exist.

Each of the database options plays a role in the server's interaction with the database and as a result will effect performance and resource utilization. This may also affect the design of the front-end application. As seen before, many of these settings must be in effect to utilize indexed views through binding.

Working from a single server, you have settings for the operating system, each of its services, the SQL Server itself, and each of its databases. That leaves us with only one major topic to discuss within the realm of advanced physical design: the implementation of system models that involve more than one machine. The topic of database systems spanning multiple machines was covered to a small extent earlier in the chapter with a look at partitioned views. As far as the capabilities of SQL Server within a multiple machine environment go, partitioned views represent only one aspect.