Configuration options can be set for:

Each of these configuration areas can be thought of as a level in the SQL Server configuration hierarchy. When an option is supported at more than one level, the applicable option setting is determined according to the following precedence order:

Note

The stored procedure sp_configure provides the option user options, which allows you to change the default values of several SET options. Although user options appears to be an instance option, it is a SET option. In previous releases of SQL Server, batch-level options are called connection-level options. When you disable Multiple Active Result Sets (MARS), batch-level options are considered connection-level options as well.

You use ALTER DATABASE to change settings for a database, sp_configure to change server-level settings, and the SET statement to change settings that affect only the current session. If there are conflicts among configuration options, the options applied later have precedence over previously set options. For example, connection options have precedence over database and server options.

Typically, SET options are configured by users within a batch or script and they apply until they are reset or the user’s session with the server is terminated. SET options can also be configured within a stored procedure or trigger. In that case, the SET options apply until they are reset inside that stored procedure or trigger, or until control returns to the code that invoked the stored procedure or trigger.

SET options are applied at either parse time or execute time. The parse-time options are QUOTED_IDENTIFIER, PARSEONLY, OFFSETS, and FIPS_FLAGGER. All other SET options are execute-time options. Parse-time options are applied during parsing as they are encountered. Execute-time options are applied during the execution of the code in which they are specified.

Batch statements are parsed in their entirety prior to execution. This means that control flow statements do not affect parse-time settings. In contrast, both control flow and execution affect whether execute-time options are set. Execute-time options are only set if control is changed to a section of the batch containing execute-time options and the related statements are executed without error. If execution fails before an execute-time option is set or during the processing or the statement that sets the option, the option is not set.

When a user connects to a database, some options may be set ON automatically. These options can be set through user options, server options, or the ODBC and OLE DB connection properties. If the user changes the SET options within a dynamic SQL batch or script, those changes apply only for the duration of that batch or script.

Note

MARS-enabled connections maintain a list of default SET option values. When a batch or script executes under that connection, the default SET option values are copied to the current request’s environment. These values remain in effect unless they are reset within the connection. Once the batch or script ends, the execution environment is copied back to the session’s default. This ensures that multiple batches executing simultaneously under the same connection run in an isolated SET options environment. However, because the execution environment is copied back to the session default when batch or script execution completes, the current default environment for a connection depends on the last batch or script that completes execution.

Table 4-4 lists the batch/connection SET options available, providing the corresponding database and server options supported in SQL Server 2005 as well as the default setting (as applicable). The SET ANSI_DEFAULTS statement is provided as a shortcut for setting SQL-92 standard options to their default values. The options that reset when this statement is used are as follows: SET ANSI_NULLS, SET CURSOR_CLOSE_ON_COMMIT, SET ANSI_NULL_DFLT_ON, SET IMPLICIT_TRANSACTIONS, SET ANSI_PADDING, SET QUOTED_IDENTIFIER, and SET ANSI_WARNINGS.

Server options can be set using the properties dialog boxes in SQL Server Management Studio or the sp_configure stored procedure. The difference between the two methods is which options are available to set. Only the most commonly used server configuration options are available through SQL Server Management Studio, but all configuration options are accessible through sp_configure. Table 4-5 lists the server options available and provides the corresponding SET options and database options that are supported in SQL Server 2005, as well as the default setting (as applicable).

Database options are set by executing the ALTER DATABASE statement. In new SQL Server installations, the settings in the model and master databases are the same. When you create new databases, the default database options for those databases are taken from the model database. Whenever you change a database option, the Database Engine recompiles everything in the database cache. Table 4-6 lists the database options available and provides the corresponding SET and server options supported in SQL Server 2005, as well as the default setting (as applicable).

By default, when you create a new database in SQL Server 2005 database, the default compatibility level is 90. When a database is upgraded to SQL Server 2005, the database retains its existing compatibility level:

Although the compatibility level of the master database cannot be modified, the compatibility level setting of the model database can be changed. This allows you to create new databases with a nondefault compatibility level. To change the compatibility level, you can use the sp_dbcmptlevel stored procedure.

The sp_dbcmptlevel stored procedure allows you to set the database compatibility level to use for a specific database. The sp_dbcmptlevel stored procedure sets certain database behaviors to be compatible with the specified earlier version of SQL Server. The following example changes the compatibility level of the Personnel database to SQL Server 7.0:

EXEC sp_dbcmptlevel "Personnel", "80";

GO

When there are possible conflicts between compatibility (and other) settings, it is important to know which database context is being used. Generally speaking, the current database context is the database defined by the USE statement if it is in a batch/script, or it is the database that contains the stored procedure if it is in a stored procedure applied to that statement. When a stored procedure is executed from a batch or another stored procedure, it is executed under the option settings of the database in which it is stored. For example, when a stored procedure in the Support database calls a stored procedure in the Personnel database, the Support procedure is executed under the compatibility level setting of the Support database and the Personnel procedure is executed under the compatibility level setting of the Personnel database.

Note

Databases with indexed views cannot be changed to a compatibility level lower than 80. CONCAT_NULL_YIELDS_NULL database option and CONCAT_NULL_YIELDS_NULL SET option settings are ignored when the compatibility level is set lower than 70.