SQL Server自定义日期截断函数性能问题及常量标记方法咨询

我讨厌在SQL Server里处理日期，总记不住一些简单操作的写法，比如截断到当日凌晨的语句：convert(datetime, datediff(day, 0, getdate()))。为了可读性和方便参考，我写了个自定义包装函数：

create or alter function
    /* Returns the date as of today at midnight (truncated)
     *
     * i.e.
     *   getdate() -> 2023-12-04 10:26
     *   date_today_midnight() -> 2023-12-04 00:00
     */
    dbo.date_today_midnight()
returns
    datetime
as begin
    return convert(datetime, datediff(day, 0, getdate()));
end

但把这个函数用在WHERE子句里时出现了严重性能问题：直接用原生语句select * from aTable where aDate >= convert(datetime, datediff(day, 0, getdate()));运行时间不到1秒，而用函数的语句select * from aTable where aDate >= date_today_midnight();运行时间超过10秒。我试过用CTE先获取日期的写法：

with dates as (select date_today_midnight() as today)
select * from aTable join dates on 1=1 where aDate >= dates.today;

性能更差，运行时间超过50秒。我知道问题和getdate()是运行时常量函数有关，SQL Server会在查询执行开始时把它替换成常量，请问怎么把自定义函数标记为运行时常量？

要让自定义函数成为运行时常量函数，推荐使用内联表值函数（SQL Server能对其做最优查询优化），也可以通过属性标记改造标量函数，具体如下：

方法1：内联表值函数（优先选择） #

内联表值函数没有BEGIN/END包裹的函数体，直接返回包含计算结果的表，SQL Server会将其逻辑直接展开到主查询中，和原生语句的执行计划完全一致：

create or alter function dbo.date_today_midnight()
returns table
with schemabinding
as return
select convert(datetime, datediff(day, 0, getdate())) as today_midnight;

使用示例：

-- 方式1：cross apply
select * 
from aTable 
cross apply dbo.date_today_midnight() dtm
where aDate >= dtm.today_midnight;

-- 方式2：子查询
select * 
from aTable 
where aDate >= (select today_midnight from dbo.date_today_midnight());

方法2：改造标量函数 #

如果坚持使用标量函数，需要添加SCHEMABINDING、RETURNS NULL ON NULL INPUT属性，并设置SYSTEM_DATAACCESS = OFF，以此告诉SQL Server该函数是运行时常量，不会访问系统数据：

create or alter function dbo.date_today_midnight()
returns datetime
with schemabinding, returns null on null input
as begin
    return convert(datetime, datediff(day, 0, getdate()));
end
go
-- 设置系统数据访问属性
alter function dbo.date_today_midnight()
with system_data_access = off;

注意：标量函数即使标记了这些属性，优化效果仍不如内联表值函数，仅作为备选方案。

原理说明 #

• 普通标量函数会被SQL Server视为逐行执行的函数，即便内部调用了getdate()这种运行时常量，也不会被提升为查询级常量，导致无法有效利用索引，性能骤降。
• 内联表值函数会被SQL Server当作视图展开，函数内的getdate()会被识别为查询级运行时常量，执行计划与原生语句一致，能正常利用索引。
• 带指定属性的标量函数，SQL Server会将其识别为运行时常量函数，在查询开始时仅计算一次，而非逐行重复计算。

内容的提问来源于stack exchange，提问作者Jamie