Почему SQL Server не использует индекс в вычисленном столбце?

Учитывая следующее в базе данных 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, который преобразует a datetime2 в часовом поясе в datetime2 в другой часовой пояс.

Когда я запускаю следующий запрос:

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

План выполнения, за которым следует SQL Server, указывает, что i не используется.

Вместо этого выполняется сканирование неявного индекса на PK, за которым следует несколько скалярных вычислений, прежде чем, наконец, фильтр, используя предикаты запроса. Планом выполнения, который я ожидал, является проверка на i.

Используйте SSDT-проект в этот файл ZIP, чтобы попытаться воспроизвести проблему. Он включает в себя ложное определение CLR UDF. Включен также план выполнения, который я получаю.

Ответы

Ответ 1

Я смог воспроизвести проблему, используя ваш подключенный проект (это, вероятно, та же проблема, что и здесь, со ссылкой на элемент здесь)

вычисляемые столбцы сначала раскрываются в основное выражение, а затем могут или не могут быть возвращены обратно в вычисленный столбец позже.

Фильтр в вашем плане показывает, что он расширяется до

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, уровень 16, состояние 1, строка 27. Процессор запросов не может произвести план запроса из-за подсказок, определенных в этом запросе. Повторно подайте запрос без указания каких-либо подсказок и без использования 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). Если аннотация применяется только к второму параметру, тогда она может вызвать поиск, хотя в плане отображается предупреждение.

введите описание изображения здесь