在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是一个CLR UDF,它将时区中的datetime2转换为另一个时区的datetime2.
当我运行以下查询：
select c1
from t
where c4 >= '1970-01-02'
and c4 <= '1970-03-04';
sql Server后面的执行计划表示我没有使用.
相反,对PK上的隐含索引进行扫描,然后是几个标量计算,最后使用查询的谓词进行过滤.我期待的执行计划是对我的扫描.
使用this ZIP file中的SSDT项目来尝试并复制问题.它包括CLR UDF的模拟定义.还包括我得到的执行计划.
解决方法
我能够使用您附加的项目来重现问题(这可能与连接项目
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],'UTC',0)),0)>=CONVERT_IMPLICIT(date,[@1],0) AND CONVERT(date,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,MaxSize=50)] string newTimeZone) { return dateTime.AddHours(-8); }
所以字符串参数变为nvarchar(50).然后它能够匹配和寻找
具体来说,第二个参数是传递需要这个文字的UTC.如果注释仅适用于第一个参数,则即使使用(forceseek)提示,该计划也不会产生搜索.如果注释仅应用于第二个参数,那么它可以产生一个搜索 – 尽管该计划显示警告.
