SQL Server 2022: Появление функции DATETRUNC, поэтому вы можете обрезать даты и прочее
Пересказ статьи Erik Darling. SQL Server 2022: Introduces the DATETRUNC Function, So You Can Truncate Your Dates And Stuffr
Важность
Когда я впервые сел за написание этой статьи, то делал забавную ошибку: я постоянно продолжал писать DATE_TRUNC.
В SQL Server это называется DATETRUNC.
Почему? Потому что так она реализована в PostgreSQL и DB2. В Oracle, конечно, она просто называется TRUNC.
Так что, хотя было бы неплохо иметь одинаковое поведение (как казалось), это точно не помогает, если у вас нет эквивалента 1:1 вызова с другими платформами.
Я предполагаю, что большинство этих дополнений в T-SQL ориентированы на кросс-платформенную разработку и миграцию.
Конечно, то, что Microsoft сильно запоздала с этой игрой, означает, что народ, вероятно, накрутил за многие годы собственные версии этих функций.
Если они назвали свою системную функцию DATE_TRUNC или даже TRUNC, у них могут возникнуть некоторые проблемы с именами объектов, которые придется решать.
Ну, да ладно. Так как она работает?
Вот несколько быстрых примеров вызова функции.
Теперь результаты:
Здесь следует отметить, что при этом не используется логика округления. Вы просто переходите к началу той единицы времени, которую выбрали. Конечно, ничего не делается с миллисекундной частью DATETIME, поскольку не хватает точности.
Но для тех, кто надеялся на появление функции SOMONTH в дополнение к функции EOMONTH, взамен вы получите это.
Работает достаточно хорошо!
Но работает ли, Darling?
Чтобы выполнить подобный тест правильно, нам потребуется индекс, чтобы сделать данные доступными для поиска.
Вы знаете для чего это делается. Чтобы быстрее искать.
Итак, поглядите, при этих совершенных обстоятельствах все работает хорошо. Но мы должны много напечатать.
Отметим, что здесь мы работаем с литеральным значением, а не значением столбца, и мы должны сообщить функции datetrunc, какой тип нам требуется посредством функции convert. В результате мы получаем простой план с поиском:
Без всего этого мы получаем план динамического поиска:
Тут есть некоторые... очевидные проблемы производительности по сравнению с вышеприведенным планом с корректными типами данных.
Постоянные читатели блога не будут удивлены, что обертка столбца таблицы новой функцией DATETRUNC вызовет старые проблемы производительности:
Это несколько раздражает, поскольку мы усекаем столбец к началу года, который должен быть доступен для поиска в индексе, поскольку это соответствует порядку сортировки данных в индексе.
Подобно большинству функций, они хорошо себя ведут в слое презентации, но ужасно в реляционном слое. В документации отсутствует информация о падении производительности, ни в примере использования функции в предложении WHERE, ни в заключительных замечаниях.
Но это обычно для любой из подобных встроенных функций.
Конечно, то, что Microsoft сильно запоздала с этой игрой, означает, что народ, вероятно, накрутил за многие годы собственные версии этих функций.
Если они назвали свою системную функцию DATE_TRUNC или даже TRUNC, у них могут возникнуть некоторые проблемы с именами объектов, которые придется решать.
Ну, да ладно. Так как она работает?
Детские игры
Вот несколько быстрых примеров вызова функции.
SELECT TOP (10)
u.DisplayName,
year =
DATETRUNC(YEAR, u.LastAccessDate),
quarter =
DATETRUNC(QUARTER, u.LastAccessDate),
month =
DATETRUNC(MONTH, u.LastAccessDate),
dayofyear =
DATETRUNC(DAYOFYEAR, u.LastAccessDate),
day =
DATETRUNC(DAY, u.LastAccessDate),
week =
DATETRUNC(WEEK, u.LastAccessDate),
iso_week =
DATETRUNC(ISO_WEEK, u.LastAccessDate),
hour =
DATETRUNC(HOUR, u.LastAccessDate),
minute =
DATETRUNC(MINUTE, u.LastAccessDate),
second =
DATETRUNC(SECOND, u.LastAccessDate),
millisecond =
DATETRUNC(MILLISECOND, u.LastAccessDate),
microsecond =
DATETRUNC(MICROSECOND, u.LastAccessDate) /* Не работает с типом datetime, поскольку там нет микросекунд */
FROM dbo.Users AS u;
Теперь результаты:
Здесь следует отметить, что при этом не используется логика округления. Вы просто переходите к началу той единицы времени, которую выбрали. Конечно, ничего не делается с миллисекундной частью DATETIME, поскольку не хватает точности.
Но для тех, кто надеялся на появление функции SOMONTH в дополнение к функции EOMONTH, взамен вы получите это.
Работает достаточно хорошо!
Но работает ли, Darling?
Не является поисковым аргументом?
Чтобы выполнить подобный тест правильно, нам потребуется индекс, чтобы сделать данные доступными для поиска.
CREATE INDEX
v
ON dbo.Votes
(CreationDate)
WITH
(
SORT_IN_TEMPDB = ON,
DATA_COMPRESSION = PAGE
);
Вы знаете для чего это делается. Чтобы быстрее искать.
Итак, поглядите, при этих совершенных обстоятельствах все работает хорошо. Но мы должны много напечатать.
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE v.CreationDate >= DATETRUNC(YEAR, CONVERT(datetime, '20130101 00:00:00.000'));
Отметим, что здесь мы работаем с литеральным значением, а не значением столбца, и мы должны сообщить функции datetrunc, какой тип нам требуется посредством функции convert. В результате мы получаем простой план с поиском:
Без всего этого мы получаем план динамического поиска:
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE v.CreationDate >= DATETRUNC(YEAR, '20130101 00:00:00.000');
Тут есть некоторые... очевидные проблемы производительности по сравнению с вышеприведенным планом с корректными типами данных.
Розыгрыши запросов
Постоянные читатели блога не будут удивлены, что обертка столбца таблицы новой функцией DATETRUNC вызовет старые проблемы производительности:
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE DATETRUNC(YEAR, v.CreationDate) >= CONVERT(datetime, '20130101 00:00:00.000');
Это несколько раздражает, поскольку мы усекаем столбец к началу года, который должен быть доступен для поиска в индексе, поскольку это соответствует порядку сортировки данных в индексе.
Подобно большинству функций, они хорошо себя ведут в слое презентации, но ужасно в реляционном слое. В документации отсутствует информация о падении производительности, ни в примере использования функции в предложении WHERE, ни в заключительных замечаниях.
Но это обычно для любой из подобных встроенных функций.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой