SET Options That Affect Results
Indexed views and indexes on computed columns store results in the database for later reference. The stored results are valid only if all connections referring to the indexed view or indexed computed column can generate the same result set as the connection that created the index. To make sure that that the stored results can be maintained correctly and return consistent results, the SET options in the following table must be set to the values shown in the Required value column whenever these conditions occur:
An index on a view or computed column is created.
A computed column is defined and PERSISTED is specified.
An INSERT, UPDATE, or DELETE operation modifies data values stored in the indexed view or indexed computed column. This includes operations such as BCP, DTS, replication, and distributed queries.
The query optimizer uses the index in the query execution plan.
For indexed views, the ANSI_NULLS and QUOTED_IDENTIFIER options must be set to ON when the view is created, because these two settings are stored with the view metadata.
SET options Required value Default server value Default OLE DB and ODBC value Default DB-Library value ANSI_NULLS
ON
OFF
ON
OFF
ANSI_PADDING
ON
ON
ON
OFF
ANSI_WARNINGS*
ON
OFF
ON
OFF
ARITHABORT
ON
ON
OFF
OFF
CONCAT_NULL_YIELDS_NULL
ON
OFF
ON
OFF
NUMERIC_ROUNDABORT
OFF
OFF
OFF
OFF
QUOTED_IDENTIFIER
ON
OFF
ON
OFF
*In SQL Server 2005, setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.
When the SET options are set incorrectly, one or more of the following can occur:
- The SQL Server 2005 Database Engine generates an error and rolls back any INSERT, UPDATE, or DELETE statement that changes data values stored in the index.
- The query optimizer does not consider the index in the execution plan of any Transact-SQL statement.
- The indexed view or computed column cannot be created.
SET Option Settings for OLE DB and ODBC Connections
Most applications use either the OLE DB Provider for SQL Server or SQL Server ODBC driver to connect to an instance of SQL Server including the SQL Server Management Studio, Integration Services, replication, and bulk copy operations. The OLE DB and ODBC default settings are correct for the six SET options that are required for indexes on views or computed columns. For the default OLE DB and ODBC values, see the previous table. These settings enforce the rules of the SQL-92 standard and are the recommended settings for SQL Server. For more information, see Client Network Configuration.
Note
Some of the SQL Server utilities set one or more of the ANSI settings to OFF to maintain compatibility with earlier versions of the utilities.
SET Option Settings for DB-Library and Embedded SQL for C Connections
DB-Library and Embedded SQL for C applications do not, by default, set any session options. Systems that use these APIs must either code the applications to issue the appropriate SET statements or change the database or server defaults to the correct settings.
Precedence for Setting Options
The ON or OFF settings for the SET options can be specified at several levels. The final setting for each session option is determined by the highest precedence operation that sets the option. The following lists the precedence of the session setting operations, with the highest precedent at the top of the list:
- Any application can explicitly override any default settings by executing a SET statement after it has connected to a server. The SET statement overrides all previous settings and can be used to turn options on and off dynamically as the application runs. The option settings are applicable only to the current connection session.
- OLE DB and ODBC applications can specify the option settings that are in effect at connection time by specifying option settings in connection strings. The option settings are applicable only to the current connection session.
- SET options specified for a SQL Server ODBC data source by using the ODBC application in Control Panel or the ODBC SQLConfigDataSource function.
- Default settings for a database. You can specify these values by using ALTER DATABASE or the Object Explorer in SQL Server Management Studio.
- Default settings for a server. You can specify these values by using either sp_configure or Object Explorer in SQL Server Management Studio to set the server configuration option named user options.
For example, the ODBC default value for ANSI_NULLS is ON; however, you can override that value by setting that option to OFF in an ODBC connection string or by using SET statements after you connect to the database.
Stored Procedures and Triggers
Stored procedures and triggers should be written to work with the six SET options that are required to support the indexes on views and computed columns. The query optimizer does not use an index on a view or computed column in SELECT statements that are executed by a stored procedure or trigger when the SET options are incorrectly set. An INSERT, UPDATE, or DELETE statement in the stored procedure or trigger that modifies data values stored in the indexed view or computed column generates an error.
Considerations
The SET statement can dynamically change the session options; therefore, issuing SET statements in a database that has indexes on views and indexed computed columns must be carefully performed. For example, an application can make a connection in which the default settings allow for an indexed view or indexed computed column to be referenced; however, if the connection calls a stored procedure or trigger that has a first statement of SET ANSI_WARNINGS OFF, this statement overrides previous defaults or settings for ANSI_WARNINGS. In this case, the optimizer ignores all indexed views or indexed computed columns when it processes any statement in the stored procedure or trigger.
Three other session options can potentially affect the format of result sets: DATEFIRST, DATEFORMAT, and LANGUAGE. Any functions whose results would be affected by changes to these options are classified as nondeterministic and cannot be used in indexed views or indexed computed columns.
See Also
Concepts
Creating Indexes on Computed Columns
Distributed Queries
General Index Design Guidelines
Index Design Basics
SET Options
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|