Twenty-two options exist at the database level to control certain behavior within that database. By default, all options are FALSE unless they were set to TRUE in the model database, in which case all databases created after the option was changed in model will have the same values. You can easily set some of these options by using SQL Server Enterprise Manager. You can set all of them directly by using the sp_dboption system stored procedure. All options correspond to bits in the status and status2 columns of sysdatabases , although those bits can also show states that can't be set directly by the database owner (such as when the database is in the process of being recovered).
Some of the database options have corresponding SET options that you can turn on or off for a particular connection. Be aware that the ODBC or OLE DB drivers will turn a number of these SET options on by default, so applications will act as though the corresponding database option had already been set. Chapter 6 goes into more detail about the SET options that are set by the ODBC and OLE DB drivers.
Executing sp_dboption with no parameters shows all options that can be set:
> EXEC sp_dboption Settable database options: ----------------------------------- ANSI null default ANSI nulls ANSI warnings auto create statistics auto update statistics autoclose autoshrink concat null yields null cursor close on commit dbo use only default to local cursor merge publish offline published quoted identifier read only recursive triggers select into/bulkcopy single user subscribed torn page detection trunc. log on chkpt.
Three of the options deal with the behavior of NULLs; these will be covered in much more detail in Chapter 6. The following list describes the meaning of each of the options:
ANSI warnings When TRUE, errors or warnings are issued when conditions such as "division by zero" or "arithmetic overflow" occur.
auto create statistics When TRUE, statistics are automatically created by the SQL Server optimizer on columns referenced in a query's WHERE clause. Adding statistics improves query performance because the SQL Server optimizer can better determine how to evaluate a query. By default, auto create statistics is true.
auto update statistics When TRUE, existing statistics are automatically updated if the data in the tables has changed. SQL Server keeps a counter of the number of modifications that have been made to a table and uses it to determine when statistics are outdated . When FALSE, existing statistics are not automatically updated; instead, they can be updated manually. By default, auto update statistics is true. We'll discuss statistics in much more detail in Chapter 14.
autoclose When TRUE, the database is closed and shut down cleanly when the last user of the database exits, thereby freeing any resources. By default, this option is automatically set to TRUE for all databases when SQL Server runs on Microsoft Windows 95 or Windows 98. When a user tries to use the database again, it automatically reopens. If the database was shut down cleanly, the database isn't initialized (reopened) until a user tries to use the database the next time SQL Server is restarted. The autoclose option is handy for desktop databases because it allows you to manage database files as normal files. You can move them, copy them to make backups , or even e-mail them to other users. However, you shouldn't use the autoclose option for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will negatively affect performance.
autoshrink When TRUE, the database files are candidates for periodic shrinking. Both data files and log files can be automatically shrunk by SQL Server. The only way to free space in the log files so that they can be shrunk is to back up the transaction log, or set trunc. log on chkpt. to TRUE . The log files shrink at the point the log is backed up or truncated.
cursor close on commit When TRUE, any open cursors are automatically closed (in compliance with SQL-92) when a transaction is committed. By default, when this setting is FALSE, cursors remain open across transaction boundaries and close only when the connection is closed or when they are explicitly closed. This setting has no effect on ROLLBACK.
dbo use only When TRUE, this option prevents all users except the database owner from subsequently using the database. Usually, this option is used only temporarily, during times when you want to make the database inaccessible to other users ”for example, when you want to change table structures and need to keep users out until finished. (If you never want some other user to access the database, you simply wouldn't add that user to the sysusers table of that database. You wouldn't use dbo use only .)
default to local cursor When TRUE, and cursors aren't specified as GLOBAL when created, the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is valid only within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or by a stored procedure output parameter. When FALSE, and cursors aren't specified as LOCAL when created, the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. We'll discuss cursors in much greater detail in Chapter 11.
merge publish When TRUE, the database can be used for merge replication publications . The option is usually set automatically when using the replication wizards to set up a database for replication.
offline This option is typically used for databases on removable media such as CDs. Placing databases on line and off line allows them to be mounted and dismounted while SQL Server is running.
published This option permits the tables of a database to be published for replication. Like merge publish , published is not normally set by a database owner, but rather by the replication wizards.
quoted identifier When TRUE, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. All strings that are delimited by double quotation marks are interpreted as object identifiers. Quoted identifiers don't have to follow the Transact -SQL rules for identifiers. They can be keywords and can include characters not normally allowed in Transact-SQL identifiers. You can't use double quotation marks to delimit literal string expressions; you must use single quotation marks to enclose literal strings. If a single quotation mark (') is part of the literal string, it can be represented by two single quotation marks ("). This setting must be TRUE if reserved keywords are used for object names in the database. When FALSE (default), identifiers can't be in quotation marks and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks. Literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes .
read only Use this option to prevent any operation in the database that would modify, insert, or delete data; create or drop database objects; or change database configuration settings. The data can be read but can't be changed in any way.
recursive triggers When TRUE, this option allows triggers to fire recursively. Indirect recursion occurs when a trigger fires and performs an action that causes a trigger on another table to fire, thereby causing an update to occur on the original table, which causes the original trigger to fire again. For example, an application updates table T1 , which causes trigger Trig1 to fire. Trig1 updates table T2 , which causes trigger Trig2 to fire. Trig2 in turn updates table T1 , which causes Trig1 to fire again. Direct recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates table T3 , which causes trigger Trig3 to fire. Trig3 updates table T3 again, which causes trigger Trig3 to fire again. When FALSE (default), triggers can't be fired recursively.
select into/bulkcopy This option allows certain nonlogged operations ”such as using the UPDATETEXT or WRITETEXT commands without logging, using SELECT INTO with a permanent table, using fast bulk copy (bcp), or performing a table load. Using nonlogged operations obviously prevents subsequent recovery from a transaction log backup. After a nonlogged operation is performed, further BACKUP LOG commands are prohibited . Instead, you can use BACKUP DATABASE to back up the entire database. (Setting this option to TRUE does not prevent transaction log backups. You must have this option on and then actually perform an unlogged operation for SQL Server to prohibit the BACKUP LOG command.)
single user This option restricts the database's use to one active SQL Server connection.
subscribed This option permits a database to subscribe to a published (replicated) database.
torn page detection When TRUE, this option causes a bit to be flipped for each 512-byte sector in a database page (8 KB) whenever the page is written to disk. This option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages. If a bit is in the wrong state when the page is later read by SQL Server, this means the page was written incorrectly; a torn page has been detected . Although SQL Server database pages are 8 KB, disks perform I/O operations using 512-byte sectors. Therefore, 16 sectors are written per database page. A torn page can occur if the system crashes (for example, because of power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. If the first sector of a database page is successfully written before the crash, it will appear that the database page on disk was updated, although it might not have succeeded. Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all. In this case, don't set torn page detection to TRUE, as it isn't needed. If a torn page is detected, the database will need to be restored from backup because it will be physically inconsistent.
trunc. log on chkpt. When this option is set, every time a checkpoint occurs (when data pages in the cache that were modified since the last checkpoint are written to disk), transactions that have already been committed ”and the log records flushed to disk ”are purged from the log. After the log is purged, you can perform BACKUP/RESTORE operations only at the database level, not at the transaction log level. But setting this option does relieve you of having to worry about truncating the transaction log to keep it from filling up. You can't disable logging, because if an operation weren't logged, it couldn't be recovered or rolled back. Most of the time, when someone wants to disable logging, the real question is, "How can I make logging invisible and not demand any administration?" The trunc. log on chkpt. option usually accomplishes this: the log simply wraps and never requires intervention. Note that the log must be large enough to accommodate the single largest transaction so that it can be recovered or rolled back. This option is often used during application development; you can also use it if your backup strategy can rely solely on database backups, not transaction log backups.
The trunc. log on chkpt. option causes the transaction log to be truncated (committed transactions are removed) every time the CHECKPOINT process occurs. When trunc. log on chkpt. is set, a checkpoint occurs for the database every time the log becomes 70 percent full. SQL Server doesn't, however, issue the checkpoint if the log can't be truncated because of an outstanding transaction. When trunc. log on chkpt. is set, SQL Server also attempts a checkpoint when a "log full" error occurs. It can be useful to select this option while doing development work to prevent the log from growing. While the trunc. log on chkpt. option is set, the transaction log can't be backed up because the truncated transaction logs in the backups can't be used to recover from media failure. Issuing the BACKUP LOG statement produces an error message that instructs you to use the BACKUP DATABASE statement. The tempdb database is always truncated by the checkpoint process even if the trunc. log on chkpt. option is set to FALSE.
The only options that you can set for the master database are autoclose , torn page detection , and trunc. log on chkpt. The tempdb database can't be set to read only , single user , or dbo use only .
Only someone with the sysadmin or dbowner role can change an option. To change an option, you must specify the database name, option, and TRUE or FALSE. You need to specify only enough of the option name to ensure its recognition (even just a single letter in the case of, say, published ). For example, to turn on published , select into/bulkcopy , and ANSI null default , you would type the following:
> EXEC sp_dboption 'testdb', 'pub', TRUE > EXEC sp_dboption 'testdb', 'select into', TRUE > EXEC sp_dboption 'testdb', 'null default', TRUE
To check that the options are set, you would type
> EXEC sp_dboption 'testdb'
and the following would appear:
The following options are set: ----------------------------------- select into/bulkcopy ANSI null default published
Executing the sp_helpdb stored procedure for a database shows some of the options that have been set to TRUE, but not all. (It shows only those options whose status bit is in sysdatabase.status and not those in sysdatabases.status2 ). However, sp_helpdb provides other useful information, such as database size , creation date, and database owner. Executing sp_helpdb with no parameters shows information about all the databases in that installation. The following databases exist on a new default installation of SQL Server, and sp_helpdb produces this output (though the created dates and sizes can vary):
> EXEC sp_helpdb name db_size owner dbid created status -------- ------- ----- ---- ----------- ---------------------- master 8.25 MB sa 1 Jun 19 1998 trunc. log on chkpt. model 1.50 MB sa 3 Jul 7 1998 no options set msdb 9.75 MB sa 4 Jul 7 1998 trunc. log on chkpt. Northwind 3.94 MB sa 6 Jul 7 1998 select into/bulkcopy, trunc. log on chkpt. pubs 2.00 MB sa 5 Jul 7 1998 trunc. log on chkpt. tempdb 8.50 MB sa 2 Jun 19 1998 select into/bulkcopy