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:
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:
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:
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.
