Using Options in SQL Server

Microsoft® SQL Server™ 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_dboptions 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.

Hierarchy of Options

When an option is supported at more than one level:

A database option overrides a server option.
A SET option overrides a database option.
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.