Using Options in SQL Server

Microsoft® SQL Server™ 2000 provides options that affect the result and performance of SQL statements. Transact-SQL allows you to set these options in the following ways:

• Server-wide configuration options (server options) are set by executing the sp_configure stored procedure.
  
  
• Database-level options (database options) are set by executing the sp_dboption stored procedure.
  
  
• The database compatibility level is set by executing the sp_dbcmptlevel stored procedure.
  
  
• Connection-level options (SET options) are specified with SET statements, such as SET ANSI_PADDING and SET ANSI_NULLS.
  
  
• Statement-level options (query hints, table hints, and join hints) are specified in individual Transact-SQL statements.

ODBC applications can specify connection options that control some of the ANSI SET options. The Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver both set several SET options by default. Options can also be set using the SQL Server Enterprise Manager.

Avoid changing SET options and setting them through the SET statements. Instead, it is recommended that SET options be set at the connection level through the connection properties of ODBC or OLE DB. Alternatively, change SET option settings using the sp_configure stored procedure.

Hierarchy of Options

When an option is supported at more than one level:

1. A database option overrides a server option.
   
   
2. A SET option overrides a database option.
   
   
3. A hint overrides a SET option.

   Note  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 a server option, it is a SET option.