绕过使用 SQL Server 索引视图时遇到的问题

• 下载示例数据库的对象和数据创建脚本 - 1.9 KB

引言

Microsoft SQL Server 提供了一个非常好的功能，称为索引视图。从数据库设计者的角度来看，它允许我强制执行可能需要通过触发器或代码来完成的约束。为了解释这一点，我将使用汽车作为例子。

自 1950 年以来制造的每辆汽车都有一个车辆识别号 (VIN)，在大多数国家（我能想到的所有国家）都需要一个更醒目的唯一标识符（车牌）。特别是对于个性化车牌，可能会使用相同的车牌标识。根据我的数据库前提，我想跟踪所有车牌、颁发日期以及分配给它们的车辆。诀窍在于，我想确保一个车牌只能分配给一辆车，一辆车在任何时候都不能分配给多个车牌，同时还要保留一个运行历史记录。所以，我需要

• 一个车辆表，显然以 VIN 作为主键，
• 一个车牌表，
• 一个“颁发者”表（例如，加利福尼亚州、德国等），
• 一个关联表，用于将唯一 ID 分配给由不同颁发者颁发的重复编号（例如，“Great”可以由所有 50 个州颁发），
• 一个表，用于将唯一的“颁发车牌” ID 分配给车辆。

如果我试图在“Vehicle_ID”、“LicensePlate_Issuer_ID”和“Current”上创建唯一索引，我最终会为每个车牌和车辆组合插入两行。它还将允许一辆车被标记为“Current”的记录超过一条。车牌也是如此。

如果我试图在“Vechicle_ID”和“Current”上创建唯一索引，那么我只能为一辆车保留两条记录，一条是当前的，一条是非当前的。如果我试图在“LicensePlate_ID”和“Current”上创建唯一索引，情况也是如此。这两者都与我想要支持的完全相反。

相反，如果我创建一个只包含我表中的“Current”记录的视图，我就可以在该视图上创建两个唯一索引，一个在“Vehicle_ID”上，另一个在“LicensePlate_Issuer_ID”上，这样我就可以得到我想要的结果，数据库会为我强制执行规则。

我使用“Current”标志的原因是为了避免日期比较（和计算），它使我能够轻松地搜索当前车牌和车辆的数据。最后，通过移除该标志，我可以使车辆或车牌成为历史记录。

这个例子很好地说明了在 SQL Server 中使用索引视图强制执行数据完整性。它也是一个很好的教训，让我们了解让数据库为您强制执行业务规则，从而帮助程序员并防止可能的错误。显然，并非所有业务规则都能在数据库中强制执行，但通常应该强制执行那些可以的。

背景

所有这些都很好；数据完整性是一件好事。问题出现在程序员被要求将数据插入到由索引视图引用的表中时。您看，如果数据库上的设置不正确，或者与数据库的连接设置不正确，您将收到类似以下的内容

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.

要在您的连接上正确设置这些选项，您需要在您的连接上运行以下命令

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

如果您使用一个集中的函数在代码中获取新连接，您可以在创建连接时使用 `is` 语句来设置这些设置。在我的例子中，我将使用我在我的密码文章中展示的 `DB` 类，因为它正是这样做的；它在一个地方获取连接。此外，该类的工作方式（尽管离生产环境还**非常**远）充分利用了连接池。因为我使用了工厂模式，所以通过更改 `App.Config` 中的 `providerName`，它可以与任何 .NET DataProvider 一起使用。这是新的 `GetConnection` 函数

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;
}

我使用 `is` 操作符来检测连接类型。明显的缺点是有人使用 `OleDb` 或 `Odbc` 提供程序连接到 SQL Server。但是，使用托管提供程序的所有好处（尤其是性能），您不会那样做，对吧？

Using the Code

使用代码实际上非常简单。如果您使用的是类似于我的 `GetConnection` 的函数，那么其他代码无需更改，一切正常，您的插入和更新也不再失败。这是一个使用我的 `DB` 类将记录插入到我的示例数据库中的简单示例，使用了我提供的测试数据

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);

关注点

是的，这可以通过以下方式避免

• 使用存储过程
• 在服务器上正确设置数据库参数
• 不使用索引视图，并将逻辑强制在程序中完成
• 不利用连接池，并将所有 SQL 通过一个连接进行路由
• 任何其他您能想到的方法

我希望这篇文章能帮助那些可能由于任何原因而无法获得这些选项的人。

历史

目前还没有。