时间戳

首页 > 解决方案 > 迁移到 SQL Server 2017：同一查询有时不返回任何行

sql-server - 迁移到 SQL Server 2017：同一查询有时不返回任何行

问题描述

我刚刚从 SQL Server 2014（仅开发环境）升级到 SQL Server 2017，并注意到一个非常奇怪的行为：相同的查询对两台服务器有两种不同的影响。

查询已被重写并减少到示例的最小值：

SELECT * FROM eth_Mandate M WHERE customerId = 1 AND M.isActive = 1

该字段isActive是一个计算列（如果授权处于活动状态，则返回 1，否则返回 0）定义为：

[isActive]  AS (CASE WHEN GETDATE() >= ISNULL([man_start],GETDATE()) AND GETDATE() <= isnull(dateadd(day,(1),[man_stop]),GETDATE()) THEN 1 ELSE 0 END)

对于 SQL Server 2014，它始终有效。对于 2017 年，4-5 次它不返回任何行，因为有时isActive计算为 0。

为什么？我已经深入搜索并想分享它，以便您可以在我根据测试理解的解释下方找到我自己的答案。

标签： sql-serverdatetimesql-server-2017getdatedatetime2

解决方案

问题在于，在 ISNULL 中，我们有一个 datetime2（字段 man_stop）和 getdate 作为后备（返回一个日期时间），并且显然 ISNULL 基于第一个参数转换了第二个参数......因此，我们正在比较 getdate( ) 作为 datetime，getdate() 作为 datetime2。请注意，man_stop（客户授权结束）大多数时候不是空的，因为授权正在运行。

然后，你必须看看这篇文章，特别是关于这句话：

至于时间部分，SQL Server 从 0 开始，并在午夜后每 0.003 秒递增数值。意味着什么 ？如果时间是 12:15:00:002 实际上存储为 12:15:00:003 但与 12:15:00.002 相比所以不相等！

解决方案 1

因此，为了解决 SQL Server 2017 的问题（尽管 SQL Server 2014 没有问题），我只是将我的字段 datetime2 转换为 datetime 以便与 getdate() 作为 datetime 进行正确比较：

[isActive]  AS (case when getdate()>=isnull(CONVERT([datetime],[man_start]),getdate()) AND getdate()<=isnull(dateadd(day,(1),CONVERT([datetime],[man_stop])),getdate()) then (1) else (0) end)

解决方案 2

或者我也可以将所有日期时间转换为 datetime2。使用返回 datetime2(7) 的 SYSDATETIME() 代替 GETDATE()。由于我的两个字段只是 datetime2 （未定义精度），因此比较更糟......但是当将 datetime2 转换为 datetime2(7) 时，它运行起来就像一个魅力。所以它变成：

(case when SYSDATETIME () >= isnull(convert(datetime2(7), [man_start]),SYSDATETIME()) AND SYSDATETIME() <= isnull(dateadd(day,(1),convert(datetime2(7), [man_stop])),SYSDATETIME()) then (1) else (0) end)

解决方案 3

将数据库兼容级别更改为 120 或更低，以在比较日期时间和日期时间 2 类型时保留旧行为。SQL Server 2016 中引入了这一重大更改。

解决方案 4（已应用解决方案）

使用相同类型的数据，避免类型转换。更改字段的类型man_start并更改man_stop为 datetime2(7)（而不是 datetime2，即 datetime2(3)）以匹配 SYSDATETIME() 函数返回的类型 (datetime2(7))。

ALTER TABLE myTable ALTER COLUMN [man_start] datetime2(7) NULL
ALTER TABLE myTable ALTER COLUMN [man_stop] datetime2(7) NULL

所以我的计算列变为： [isActive] AS (CASE WHEN SYSDATETIME() >= ISNULL([man_start],SYSDATETIME()) AND SYSDATETIME() <= isnull(dateadd(day,(1),[man_stop]),SYSDATETIME ()) 然后 1 否则 0 结束)

优点：保持兼容级别，避免类型转换（更好的性能）

推荐阅读