主页 / user-7257

GarethD's questions

Martin Hope
GarethD
Asked: 2013-10-25 00:31:33 +0800 CST
  • 25

我在一个表上有一个持久计算列，它只是由连接列组成，例如

CREATE TABLE dbo.T 
(   
    ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D DATE NULL,
    E VARCHAR(20) NULL,
    Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL 
);

这Comp不是唯一的，并且 D 是 的每个组合的有效起始日期A, B, C，因此我使用以下查询来获取每个组合的结束日期A, B, C（基本上是相同 Comp 值的下一个开始日期）：

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1
WHERE   t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY t1.Comp;

然后我在计算列中添加了一个索引来帮助这个查询（以及其他查询）：

CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL;

然而，查询计划让我感到惊讶。我会认为，因为我有一个 where 子句说明了这一点，D IS NOT NULL并且我正在排序Comp，并且没有引用索引之外的任何列，所以计算列上的索引可用于扫描 t1 和 t2，但我看到了一个聚集索引扫描。

在此处输入图像描述

所以我强制使用这个索引来看看它是否产生了一个更好的计划：

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1 WITH (INDEX (IX_T_Comp_D))
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;

哪个给出了这个计划

在此处输入图像描述

这表明正在使用 Key 查找，其详细信息是：

在此处输入图像描述

现在，根据 SQL-Server 文档：

如果在 CREATE TABLE 或 ALTER TABLE 语句中将该列标记为 PERSISTED，则可以在使用确定性但不精确的表达式定义的计算列上创建索引。这意味着数据库引擎将计算值存储在表中，并在计算列所依赖的任何其他列更新时更新它们。数据库引擎在为列创建索引以及在查询中引用索引时使用这些持久化值。当数据库引擎无法准确证明返回计算列表达式的函数（尤其是在 .NET Framework 中创建的 CLR 函数）是否具有确定性和精确性时，此选项使您能够在计算列上创建索引。

因此，如果，正如文档所说“数据库引擎将计算值存储在表中”，并且该值也存储在我的索引中，为什么在未引用 A、B 和 C 时需要进行键查找来获取它们查询呢？我假设它们被用来计算 Comp，但是为什么呢？另外，为什么查询可以在 上使用索引t2，但不能在 上使用t1？

SQL Fiddle 上的查询和 DDL

注意我已经标记了 SQL Server 2008，因为这是我的主要问题所在的版本，但我在 2012 年也得到了相同的行为。

sql-server sql-server-2008