SQL Server : compare only month and day - SARGable

Question

我有一个表存储 datetime列，已编入索引。我试图找到一种方法来只比较月份和日期(完全忽略年份)。

只是为了记录，我想说我已经在使用 MONTH() 和 DAY() .但是我遇到了我当前的实现使用索引扫描而不是索引搜索的问题，因为在这两个函数中直接使用列来获取月和日。

可以有两种类型的引用进行比较:固定的给定日期和今天 (GETDATE()) .日期将根据时区进行转换，然后提取其月份和日期，例如

DECLARE @monthValue DATETIME = MONTH(@ConvertDateTimeFromServer_TimeZone);
DECLARE @dayValue DATETIME = DAY(@ConvertDateTimeFromServer_TimeZone); 

另一点是该列存储具有不同年份的日期时间，例如

1989-06-21 00:00:00.000
1965-10-04 00:00:00.000
1958-09-15 00:00:00.000
1965-10-08 00:00:00.000
1942-01-30 00:00:00.000

现在问题来了。如何创建 SARGable 查询以获取表中与给定月份和日期匹配的行，而不考虑年份，但也不涉及任何函数中的列？网络上的现有示例使用年份和/或日期范围，这对我来说根本没有帮助。

示例查询:

Select t0.pk_id 
From dob t0 WITH(NOLOCK) 
WHERE ((MONTH(t0.date_of_birth) = @monthValue AND DAY(t0.date_of_birth) = @dayValue))

我也试过 DATEDIFF()和 DATEADD() ，但它们都以索引扫描结束。

Answer 1

添加到我在 Calendar Table 上发表的评论中.

这可能是获取 SARGable 查询的最简单方法。正如您所发现的，MONTH([YourColumn])和 DATEPART(MONTH,[YourColumn])两者都会导致您的查询变得不可 SARGable。

考虑到您的所有列(至少在您的示例数据中)的时间为 00:00:00这对我们有利，因为它们实际上只是日期。这意味着我们可以轻松地JOIN使用类似以下内容的日历表:

SELECT dob.[YourColumn]
FROM dob
     JOIN CalendarTable CT ON dob.DateOfBirth = CT.CalendarDate;

现在，如果我们使用上述文章中的表格，您将创建一些额外的列( MonthNo 和 CDay ，但是，您可以随意称呼它们)。然后，您可以将这些列添加到您的查询中:

SELECT dob.[YourColumn]
FROM dob
     JOIN CalendarTable CT ON dob.DateOfBirth = CT.CalendarDate
WHERE CT.MonthNo = @MonthValue
  AND CT.CDay = @DayValue;

如您所见，这是一个更可搜索的查询。

如果你想处理闰年，你可以使用 CASE 添加更多的逻辑。表达:

SELECT dob.[YourColumn]
FROM dob
     JOIN CalendarTable CT ON dob.DateOfBirth = CT.CalendarDate
WHERE CT.MonthNo = @MonthValue
  AND CASE WHEN DATEPART(YEAR, GETDATE()) % 4 != 0 AND CT.CDat = 29 AND CT.MonthNo = 2 THEN 28 ELSE CT.Cdat END = @DayValue;

在不是闰年的年份(当 DATEPART(YEAR, GETDATE()) % 4 != 0 时)，这会将 2 月 29 日的某人的生日视为 2 月 28 日。

也可能值得注意的是，将 DateOfBirth 列更改为 date 可能是值得的。 .出生日期不是在给定的时间，而是在给定的日期；这意味着没有来自 datetime 的隐式转换至 date在您的日历表上。

编辑:另外，刚刚注意到，你为什么使用 NOLOCK ?你做 知道 那是做什么的，对吧..？除非您对脏读和鬼数据感到满意？