Миграция на SQL Server 2017: один и тот же запрос иногда не возвращает строк

Question

Я только что обновился до SQL Server 2017 с SQL Server 2014 (пока только для среды разработки) и заметил очень странное поведение: один и тот же запрос имеет два разных влияния на оба сервера.

Запрос был переписан и сведен к минимуму для примера:

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.

Зачем? Я глубоко искал и хотел поделиться им, чтобы вы могли найти мой собственный ответ ниже объяснения, которое я понял, основываясь на моих тестах.

Answer 1

Проблема в том, что в ISNULL у нас есть datetime2 (поле man_stop) и getdate как резервный (возвращает datetime), и, соответственно, 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

Или я мог бы также преобразовать все datetime в datetime2. Вместо GETDATE() используйте SYSDATETIME(), возвращая datetime2(7). Поскольку мои два поля просто 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 или ниже, чтобы сохранить устаревшее поведение при сравнении типов datetime и datetime 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 (СЛУЧАЙ, КОГДА SYSDATETIME() >= ISNULL([man_start],SYSDATETIME()) И SYSDATETIME() <= isnull(dateadd(день,(1),[man_stop]),SYSDATETIME()) ТОГДА 1 ИЛИ 0 КОНЕЦ)

Преимущества: сохраняется уровень совместимости, избегайте преобразования типов (лучше производительность)