Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server

Working Around Issues When Using SQL Server Indexed Views

Rate me:
Please Sign up or sign in to vote.
4.82/5 (7 votes)
29 Jan 2010CPOL4 min read 55.6K   159   19   6
Working around issues when using SQL Server indexed views.

Introduction

Microsoft SQL Server provides a really nice feature called indexed views. From a database designer's point of view, it allows me to enforce constraints that might otherwise need to be done as triggers or enforced in code. To explain this, I will use an example of an automobile.

Every automobile made since 1950 has a Vehicle Identification Number (VIN), and in most countries (all of them I can think of), a more visible unique identifier (a license plate) is required. Especially with personalized license plates, the same plate identification may be used. For the premise of my database, I want to keep track of all license plates, dates they were issued, and which vehicle they are issued to. The trick is I want to make sure that a license plate is only allowed to be assigned to a single car, and that a car may not be assigned to more than a single plate at a time, all while keeping a running history. So, I need:

  • a table for the vehicles, obviously with the VIN as the primary key,
  • a license plate table,
  • a table of "issuers" (i.e., California, Germany, etc.),
  • an association table for assigning a unique ID to duplicate numbers issued by different issuers (i.e., "Great" could be issued by all 50 states),
  • a table to assign the unique "issued plate" ID with a vehicle.

Had I tried to put a unique index on "Vehicle_ID", "LicensePlate_Issuer_ID", and "Current", I would have ended up with being able to put in two rows for each license plate and vehicle combination. It would have also allowed more than a single record for a car to be marked as "Current". The same is true for license plates.

Had I tried to put a unique index on "Vechicle_ID" and "Current", then I would only be able to have two rows for a vehicle, one current and one not. The same is true if I had tried to put a unique index on "LicensePlate_ID" and "Current". Both of those are quite contrary to what I want to support.

If, instead, I create a view of only the "Current" records from my table, I can create two unique indexes on it, one on "Vehicle_ID" and another on "LicensePlate_Issuer_ID", then I get exactly what I was after, and the database enforces the rules for me.

The reason I used a "Current" flag is to avoid date comparisons (and math), and it enables me to easily search the data for the current license plate and vehicle. Lastly, by removing the flag, I can make a vehicle or plate historical.

This example is a good example of using indexed views to enforce data integrity in SQL Server. It also is a nice lesson for letting the database do the work of enforcing business rules for you, helping programmers out, and preventing possible errors. Obviously, not every single business rule can be enforced in the database, but those that can usually should be.

Background

All of that is well and good; data integrity is a great thing. The problem comes in when a programmer is asked to insert data into a table referenced by an indexed view. You see, if you don't have the settings properly set on the database, or the connection to the database, you will receive something like the following:

INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Verify that SET options are correct for use with indexed views and/or indexes on
computed columns and/or filtered indexes and/or query notifications and/or XML data
type methods and/or spatial index operations.

To set these options correctly on your connection, you need to run the following on your connection:

SQL
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

If you are using a centralized function to get a new connection in your code, you can use the is statement to set these settings when you make the connection. For my example, I am going to use my DB class that I showed in my password article because it does exactly that; it gets a connection in a single spot. Furthermore, the way the class works (while it is still very short of production worthy), it takes full advantage of connection pooling. Because I am using the Factory pattern, it can used with any .NET DataProvider simply by changing the providerName in the App.Config. Here is the new GetConnection function:

C#
private static DbConnection GetConnection()
{
    DbConnection conn = Factory.CreateConnection();

    conn.ConnectionString = ConnectionString;
    conn.Open();
    if(conn is System.Data.SqlClient.SqlConnection)
    {
        using(var cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText =
                @"
                    SET ANSI_NULLS ON
                    SET ANSI_PADDING ON
                    SET ANSI_WARNINGS ON
                    SET ARITHABORT ON
                    SET CONCAT_NULL_YIELDS_NULL ON
                    SET QUOTED_IDENTIFIER ON
                    SET NUMERIC_ROUNDABORT OFF";
            cmd.ExecuteNonQuery();
        }
    }
    return conn;
}

I am using the is operator to detect the connection type. The obvious shortcoming here is someone using the OleDb or Odbc providers to connect to SQL Server. However, with all the benefits of using the managed provider (not the least of which is performance), you wouldn't do that, right?

Using the Code

Using the code is actually amazingly simple. If you are using a similar function to my GetConnection, then no other code has to change, it all works happily, and your inserts and updates no longer fail. Here is a simple example using my DB class to insert a record into my example database, using the test data I have provided:

C#
List<DbParameter> parameters = new List<DbParameter>();
StringBuilder sb = new StringBuilder();
sb.AppendLine(
@"INSERT INTO [dbo].[Vehicle_LicensePlate_Issuer] ([LicencePlate_Issuer_ID]
,[Vehicle_ID]
,[Date Issued]
,[Current]) VALUES (@vpi, @lpi, @di, @c);");

var p = DB.Factory.CreateParameter();
p.ParameterName = "vpi";
p.Value = new Guid("58b49747-2cf2-4e2b-a0d2-12d3a109de3e");
p.DbType = DbType.Guid;

parameters.Add(p);

p = DB.Factory.CreateParameter();
p.ParameterName = "lpi";
p.Value = new Guid("26586578-3c29-4300-b3ff-164f5bec5668");
p.DbType = DbType.Guid;

parameters.Add(p);

p = DB.Factory.CreateParameter();
p.ParameterName = "di";
p.Value = DateTime.UtcNow;
p.DbType = DbType.DateTime;

parameters.Add(p);
p = DB.Factory.CreateParameter();
p.ParameterName = "c";
p.Value = true;
p.DbType = DbType.Boolean;

parameters.Add(p);

DB.ExecuteNonQuery(sb.ToString(), parameters);

Points of Interest

Yes, this could be avoided by:

  • using Stored Procedures
  • setting the database parameters correctly on the server
  • not using indexed views, and forcing the logic to be done programmatically
  • not taking advantage of connection pooling, and funneling all SQL through a single connection
  • whatever else you can think up

I hope this article will help those that may not have those options available, whatever the reason may be.

History

None so far.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Pengdows
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralBusiness Logic in the Database Pin
aureolin11-Feb-10 5:48
aureolin11-Feb-10 5:48 
GeneralRe: Business Logic in the Database Pin
Alaric Dailey11-Feb-10 6:16
Alaric Dailey11-Feb-10 6:16 
GeneralOther tips... Pin
MR_SAM_PIPER1-Feb-10 17:25
MR_SAM_PIPER1-Feb-10 17:25 
You can also configure the connection options on the server instance instead of requiring clients to do this whenever they create a SQL connection.

In SQL Server Management Studio, right-click the server node, select Properties, then in the Connections page you can set the default connection options that will be set for every new connection to that server. I've found this to be the easiest way to add support for indexed views as it doesn't require changes on the client.

Also, the query optimizer seems to take a weak regard for indexed views, even when they are built primarily to denormalize data and make it faster to query and return large resultsets. I once built an indexed view over a group of 6 tables, and found that SQL Server would always expand the query plan to use the base tables instead of the index on the view.

In that scenario I had to use the NOEXPAND hint on the view to get the best query plan - there were a couple of orders of magnitude in improved performance after that.
GeneralRe: Other tips... Pin
Alaric Dailey1-Feb-10 17:50
Alaric Dailey1-Feb-10 17:50 
GeneralRe: Other tips... Pin
MR_SAM_PIPER1-Feb-10 17:57
MR_SAM_PIPER1-Feb-10 17:57 
GeneralRe: Other tips... Pin
Alaric Dailey2-Feb-10 3:02
Alaric Dailey2-Feb-10 3:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.