SET Options That Affect Results
Indexed views and indexes on computed columns involve storing results in the database for later reference. These stored results are valid only if all connections referring to the results can generate the same result set as the connection that created the stored result set.
Indexed Views
Indexed views store the result set returned by a view by creating a clustered index on the view. For complex views, the stored result set greatly speeds data retrieval. An indexed view is useful only as long as all operations referencing the view use exactly the same algorithms when building their results. Like indexes for computed columns, this includes:
- The CREATE INDEX statement that first builds the result set.
- Any subsequent INSERT, UPDATE, or DELETE statements that affect the base data used to build the view result set.
- All queries for which the optimizer must determine if the indexed view will be useful.
Indexes on Computed Columns
Indexes on computed columns must calculate the computed column values to build the keys stored in the index. An index on a computed column works only as long as all operations using the index use exactly the same algorithms to determine the key values:
- The original CREATE INDEX statement that establishes the first set of key values.
- As later INSERT, UPDATE and DELETE statements create, alter, or remove key values, the operations are not valid unless the key values are computed with the same algorithms used by the original create index operation.
- For the index to be useful for any subsequent statement, all of the key values stored in the index must be the same as would be generated by the current settings of the connection executing the statement.
SET Option Settings
Any SET options that affect the results generated by Transact-SQL statements must have the same settings for all operations referencing the index. There are seven SET options that affect the results stored in computed columns and returned by views. All connections using indexes on computed columns or indexed views must have the same settings for these seven options:
- These six SET options must be set to ON:
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- ARITHABORT
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIER
- ANSI_NULLS
- The NUMERIC_ROUNDABORT option must be set to OFF.
These SET options must be set correctly for any connection that creates an index on a view or computed column. Any connection executing INSERT, UPDATE or DELETE statements that change data values stored in the indexes must have the correct settings. This includes bulk copy, Data Transformation Services (DTS), and replication operations. Microsoft® SQL Server™ 2000 generates an error and rolls back any insert, update, or delete operation attempted by a connection that does not have the proper option settings. The optimizer does not consider using an index on a computed column or view in the execution plan of any Transact-SQL statement if the connection does not have the correct option settings.
For example, a table is defined and populated using this script:
CREATE TABLE Parts
(PartID int PRIMARY KEY,
PartName char(10),
PartMaterial char(10),
PartColor char(10),
PartDescription AS PartMaterial + PartColor
)
GO
INSERT INTO Parts VALUES (1, 'Table', 'Wood', 'Red')
INSERT INTO Parts VALUES (2, 'Chair', 'Fabric', 'Blue')
INSERT INTO Parts VALUES (3, 'Bolt', 'Steel', NULL)
GO
The value calculated for the PartDescription column for the row, where PartID is 3, depends on the CONCAT_NULL_YIELDS_NULL option. If CONCAT_NULL_YIELDS_NULL is set to ON, the calculated value is NULL. If CONCAT_NULL_YIELDS_NULL is set to OFF, the calculated value is the string 'Steel'. For an index on the PartDescription column to be properly maintained, all INSERT, UPDATE, and DELETE operations must have the same setting of CONCAT_NULL_YIELDS_NULL ON as the connection that created the index. The index is also not used by the optimizer for any connection with a different CONCAT_NULL_YIELDS_NULL setting from the connections that created the key values.
SET Option Settings for OLE DB and ODBC Connections
Six of the seven SET option settings required for indexes on computed columns and views are the default settings for the OLE DB Provider for SQL Server and the SQL Server ODBC driver. These settings are:
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- CONCAT_NULL_YIELDS_NULL
- NUMERIC_ROUNDABORT
- QUOTED_IDENTIFIER
These settings also enforce the rules of the SQL-92 standard and are the recommended settings for SQL Server. Because DTS, replication, and bulk copy operations in SQL Server 2000 use OLE DB or ODBC, these options are also automatically set for these operations. 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 ARITHABORT ON is the one option that is not automatically set for connections using the OLE DB Provider for SQL Server or the SQL Server ODBC driver. OLE DB and ODBC connections do not specify an ARITHABORT setting, so connections default to the server default, which is ARITHABORT OFF. This server default is controlled by the user options server option. The user options bit that equates to 64 should be set for any server on which you implement indexes on views or computed columns. For more information about how to set this option, see user options Option.
Precedence for Setting Options
The settings for the SET options can be specified at several levels. The final setting for each session option for a particular connection is determined by the highest precedence operation that sets the option. The precedence of the sessionsetting operations is (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 executes.
- OLE DB and ODBC applications can specify the option settings that are in effect at connection time by specifying option settings in connection strings.
- You can SET options to ON or OFF for any SQL Server ODBC data source by using the ODBC application in Control Panel, or the ODBC SQLConfigDataSource function. Any connection made by an ODBC application using that data source uses the specified defaults, unless the application overrides the defaults in the connect string or with SET statements after connecting.
- The OLE DB Provider for SQL Server and the SQL Server ODBC driver automatically set the seven session options to the settings required for indexed views. DB-Library and Embedded SQL for C applications do not, so systems using these APIs must either code the applications to issue the proper SET statements or change the database or server defaults to the correct settings.
- You can establish default settings for a database using ALTER DATABASE or SQL Server Enterprise Manager.
- You can establish default settings for a server by using either sp_configure or SQL Server Enterprise Manager to set the server configuration option named user options. For more information, see user options Option.
The connection option settings required for indexed views and indexes on computed columns must be active:
- For any connection that creates an index on a view or computed column.
- For any INSERT, UPDATE, or DELETE statements that attempt to modify data covered by an index on a view or computed column.
- Before the optimizer can consider using an index on a view or computed column to cover a query.
- 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 as object properties with the view definition.
Considerations
The SET statement can change the options dynamically; therefore, issuing SET statements in a database that has indexes on views and computed columns must be done carefully. For example, an application can make a connection in which the default settings allow an indexed view to be referenced. If the connection calls a stored procedure whose first statement is SET ANSI_WARNINGS OFF, that statement overrides previous defaults or settings for ANSI_WARNINGS. The optimizer ignores all indexed views or indexes on computed columns when processing any statement in the stored procedure. Any statements in the stored procedure that attempted an INSERT, UPDATE, or DELETE that affected an indexed view or an index on a computed column generate an error.
The logic in some stored procedures or triggers originally developed in earlier versions of SQL Server depends on options such as QUOTED_IDENTIFIER or ANSI_NULLS being set to OFF. Also, DB-Library and Embedded SQL for C applications do not, by default, set any session options. Connections from these applications can create problems for other stored procedures or triggers that depend on the options being set to ON. The recommended solution has been to code SET statements at the start of either of these types of stored procedures and triggers to ensure they had the operating environment they required. In SQL Server 2000, if a stored procedure or trigger sets any of the options needed by indexes on views and computed columns to a value other than those required by the indexes, the indexes are not used to cover any SELECT statements executed by the stored procedure or trigger. Any INSERT, UPDATE, or DELETE statements executed by these stored procedures and triggers fails if they modify data covered by an index on a view or computed column. In SQL Server 2000 instances that use indexes on views and computed columns, stored procedures and triggers should be written to work with the seven SET options needed to support these indexes. SET statements should be used only in stored procedures and triggers for these systems if they receive connections from clients using DB-Library, Embedded SQL for C, or ODBC drivers from SQL Server version 6.5 or earlier. The stored procedures and triggers should set only the options to those required by indexes on views and computed columns.
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 views or computed columns that are indexed.