Why doesn't SQL Server use the index on the computed column?

Question

Given the following in a SQL Server 2014 DB:

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 is a CLR UDF that converts a datetime2 in a time zone to a datetime2 in another time zone.

When I run the following query:

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

The execution plan followed by SQL Server indicates that i isn't used.

Instead there is a scan on the implicit index on the PK followed by a couple of scalar computations before finally a filter using the predicates of the query. The execution plan that I was expecting is a scan on i.

Use the SSDT project in this ZIP file to try and replicate the problem. It includes a mock definition of the CLR UDF. Included also is the execution plan I get.

Answer 1

I was able to reproduce the issue using your attached project (it's probably the same issue as here with connect item here)

computed columns are first expanded out to the underlying expression and then may or may not be matched back to the computed column later.

The filter in your plan shows that it gets expanded out to

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)

These implicit casts to nvarchar(max) appear to be doing the damage. A simple repro that does not require CLR is

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.

If I change the function definition to

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

So the string parameters become nvarchar(50). Then it is able to match and give a seek

Specifically it is the second parameter that is being passed the literal UTC that requires this. If the annotation is applied to only the first parameter then the plan will not produce a seek even with the with (forceseek) hint. If the annotation is applied to only the second parameter then it can produce a seek - though the plan shows a warning.