Congratulations to Byron Hynes, systems analyst for Tamarack Computers in Yellowknife, Canada, and John Northfield, SQL Server DBA for ANZ Banking Group in Melbourne, Australia. Byron won first prize of $100 for the best solution to the September Reader Challenge, "Creating Indexed Views." John won second prize of $50. Here's a recap of the problem and the solution to the September Reader Challenge.
Problem
As the database architect for several SQL Server 2000 databases, Nick faces performance problems with several ad hoc queries that perform complex joins and aggregations. He decides that creating a few indexed views will generate significant performance gains without requiring him to modify most of the existing code. This solution should be effective because the query optimizer can rewrite the queries dynamically to use the indexed views rather than querying the larger base tables. However, after he creates the indexed views, he realizes that they change the way the code interacts with the base tables and performs the ad hoc queries.
Failure of the stored procedures that modify the base tables in the indexed views is Nick's first hint that creating the indexed views has changed the code. (The applications that invoke the stored procedures and modify the base tables connect to SQL Server by using the ODBC, OLE DB, or ADO APIs.) When Nick tries to invoke some of the code, he receives the following sample error message:
Server: Msg 1934, Level 16, State 1, Line 1 INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER,ARITHABORT'.
Help Nick take the necessary steps to ensure that the stored procedures and any code he uses on the base tables work properly after he creates the indexed views.
Solution
The query optimizer in SQL Server 2000 Enterprise and Developer editions typically can use indexed views during execution to avoid joining against larger base tables and to perform already-calculated aggregations in the views. However, in Nick's scenario, creating the indexed views breaks existing code because the various APIs don't enable all of the SET options required for successful usage of the indexed views. The SET options are set incorrectly because:
- The client or user establishes SET options such as QUOTED_IDENTIFIER and ANSI_NULLS at parse time only (before SQL Server is connected to the application) and persist with the stored procedures at creation time.
- The ODBC, OLE DB, and ADO APIs use default SET options different from the options needed to execute the data modifications successfully. While Nick's API is connecting the application to SQL Server, the application assumes that the API has all the SET options enabled for the new script (including options such as QUOTED_IDENTIFIER), when in fact the API has only the default options enabled.
Nick can solve the SET-options problem by taking the following steps at the time he creates the indexed views:
- He recreates the affected stored procedures after he enables the correct SET options. In his case, the options are QUOTED_IDENTIFIER and ANSI_NULLS-the only options persisted with the stored procedure.
- He modifies the applications that access the database containing the indexed views so that the applications can set the required options after connection. He uses the SET command to enable the required options. And by enabling the SET options proactively, Nick can ensure that the application's behavior is consistent no matter which API and server configuration are in use.
The SET options that Nick needs to enable correctly in all connections are:
ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER
Nick also needs to set the NUMERIC_ROUNDABORT option to OFF.
Now, test your SQL Server savvy in the October Reader Challenge, "Importing IP Information" (below). Submit your solution in an email message to [email protected] by September 18. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.
Here's the challenge: For reporting purposes, Martin uses bulk copy program (bcp) to import Web data about IP networks into a SQL Server 2000 database. Each row in the data file contains a range of IP addresses that defines a network and some of the network's characteristics such as the network type (e.g., Class A, Class B). The bcp format-file information below includes the data file's relevant columns:
8.0
2
1 SQLCHAR 0 15 "\t" 1 StartIP
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 15 "\r\n" 2 EndIP
SQL_Latin1_General_CP1_CI_AS
Martin also needs to define indexes that will let SQL Server perform range searches efficiently on these networks or IP address ranges. Let's assume the following table schema:
CREATE TABLE IPs ( StartIP varchar(15) NOT NULL,
EndIP varchar(15) )
Help Martin design an efficient solution that can perform the data import with no modifications to the input file and optimize the table for queries.
