sql-server - 为什么 SQL Server 不在计算列上使用索引？

Question

在 SQL Server 2014 数据库中给出以下内容:

create table t 
(
    c1 int primary key,
    c2 datetime2(7),
    c3 nvarchar(20),
    c4 as cast(dbo.toTimeZone(c2, c3, 'UTC') as date) persisted
);

create index i on t (c4);

declare @i int = 0;

while @i < 10000 
begin
    insert into t (c1, c2, c3) values
        (@i, dateadd(day, @i, '1970-01-02 03:04:05:6'), 'Asia/Manila');
    set @i = @i + 1;
end;

toTimeZone 是一个 CLR UDF，用于将一个时区中的 datetime2 转换为另一个时区中的 datetime2。

当我运行以下查询时:

select c1 
from t 
where c4 >= '1970-01-02'
    and c4 <= '1970-03-04';

SQL Server 后面的执行计划表明未使用i。

相反，会对 PK 上的隐式索引进行扫描，然后进行几次标量计算，最后使用查询的谓词进行过滤。我期望的执行计划是对 i 的扫描。

使用this ZIP file中的SSDT项目尝试并复制问题。它包括 CLR UDF 的模拟定义。还包括我得到的执行计划。

Answer 1

我能够使用您附加的项目重现该问题(这可能与带有连接项 here 的 here 相同)

计算列首先扩展到基础表达式，然后可能会也可能不会匹配回计算列。

您的计划中的过滤器显示它已扩展到

CONVERT(date,[computed-column-index-problem].[dbo].[toTimeZone](CONVERT_IMPLICIT(datetime,[computed-column-index-problem].[dbo].[t].[c2],0),CONVERT_IMPLICIT(nvarchar(max),[computed-column-index-problem].[dbo].[t].[c3],0),CONVERT_IMPLICIT(nvarchar(max),'UTC',0)),0)>=CONVERT_IMPLICIT(date,[@1],0) 
AND 
CONVERT(date,[computed-column-index-problem].[dbo].[toTimeZone](CONVERT_IMPLICIT(datetime,[computed-column-index-problem].[dbo].[t].[c2],0),CONVERT_IMPLICIT(nvarchar(max),[computed-column-index-problem].[dbo].[t].[c3],0),CONVERT_IMPLICIT(nvarchar(max),'UTC',0)),0)<=CONVERT_IMPLICIT(date,[@2],0)

这些对 nvarchar(max) 的隐式转换似乎正在造成损害。不需要 CLR 的简单重现是

DROP TABLE IF EXISTS t 
DROP FUNCTION IF EXISTS [dbo].[toTimeZone]

GO

CREATE FUNCTION [dbo].[toTimeZone] (@newTimeZone [NVARCHAR](max))
RETURNS DATE
WITH schemabinding
AS
  BEGIN
      RETURN DATEFROMPARTS(1970, 01, 02)
  END

GO

CREATE TABLE t
  (
     c1 INT IDENTITY PRIMARY KEY,
     c4 AS dbo.toTimeZone(N'UTC') persisted
  );

CREATE INDEX i
  ON t (c4);

INSERT INTO t
DEFAULT VALUES

SELECT c1
FROM   t WITH (forceseek)
WHERE  c4 >= '1970-01-02'
       AND c4 <= '1970-03-04'; 

Msg 8622, Level 16, State 1, Line 27 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

如果我将函数定义更改为

public static DateTime toTimeZone(DateTime dateTime,
    [SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=50)]
    string originalTimeZone,
    [SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=50)]
    string newTimeZone)
{
    return dateTime.AddHours(-8);
}

因此字符串参数变为nvarchar(50)。然后它就能够匹配并给出搜索

具体来说，传递的文字 UTC 是需要此参数的第二个参数。如果注释仅应用于第一个参数，则即使使用 with (forceseek) 提示，计划也不会产生搜索。如果注释仅应用于第二个参数，那么它可以产生搜索 - 尽管计划显示警告。