Using Options in SQL Server

  • Article

Microsoft SQL Server 2005 provides options that affect the result and performance of SQL statements. You can set these options in the following ways:

  • To set instance-wide configuration options, use the sp_configure stored procedure.

  • To set database-level options, use the ALTER DATABASE statement.

  • To set the database compatibility level, use the sp_dbcmptlevel stored procedure.

  • To specify batch-level options (SET options), use the SET statements, such as SET ANSI_PADDING and SET ANSI_NULLS.

    Note

    Batch-level options are referred to as connection-level options in earlier versions of SQL Server and also in connections that have disabled Multiple Active Result Sets (MARS).

  • To specify statement-level options, such as query hints, table hints, and join hints, use the individual Transact-SQL statements. For more information, see Hints (Transact-SQL).

ODBC applications can specify connection options that control some of the ANSI SET options. Both the SQL Native Client OLE DB Provider for SQL Server and SQL Native Client ODBC driver set several SET options by default.

You should avoid changing SET options and setting them through the SET statements. Instead, we recommend that SET options be set at the connection level through the connection properties of ODBC or OLE DB. Alternatively, you can change SET option settings by using the sp_configure stored procedure.

sp_configure provides the option user options. This lets you change the default values of several SET options. Although user options appears to be an instance option, user options is a SET option.

Hierarchy of Options

When an option is supported at more than one level, the following hierarchy is imposed:

  1. A database option overrides an instance option.
  2. A SET option overrides a database option.
  3. A hint overrides a SET option.

Note

SET options set within a dynamic SQL batch affect only the scope of that batch.

Note

SET options, such as QUOTED_IDENTIFIER and ANSI_NULLS, are persisted with stored procedure definition and, therefore, take precedence over different values explicitly set for them.

See Also

Concepts

SET Options
Database Options
Instance Options
Database Compatibility Level Option

Help and Information

Getting SQL Server 2005 Assistance