Я часто сталкиваюсь с необходимостью генерирования строк на лету. И не только для простого получения последовательности чисел или дат, но и, например, для наполнения базы данных тестовыми данными, создания сводных таблиц (pivot) с произвольным количеством столбцов, экстраполяции данных, заполнения пропусков в диапазонах дат или времени.
При работе с SQL Server 2022 или Azure SQL Database, можно воспользоваться недавно появившейся в T-SQL встроенной функцией GENERATE_SERIES. Использовать её довольно просто: передаёте обязательные граничные значения start и stop, и необязательный шаг step:
SELECT value FROM GENERATE_SERIES(<start>, <stop> [, <step>]);Несколько небольших примеров:
/* count to 6 */
SELECT [1-6] = value
FROM GENERATE_SERIES(1, 6);
/* count by 5s to 30 */
SELECT [step 5] = value
FROM GENERATE_SERIES(5, 30, 5);
/* count from 10 to 0, backwards, by 2 */
SELECT [backward] = value
FROM GENERATE_SERIES(10, 0, -2);
/* get all the days in a range, inclusive */
DECLARE @start date = '20230401',
@end date = '20230406';
SELECT [days in range] = DATEADD(DAY, value, @start)
FROM GENERATE_SERIES(0, DATEDIFF(DAY, @start, @end));
Результаты:
1-6 (первый запрос) |
| step 5 (второй запрос) |
| backward (третий запрос) |
| days in range (четвёртый запрос) |
1 | 5 | 10 | 2023-04-01 | |||
2 | 10 | 8 | 2023-04-02 | |||
3 | 15 | 6 | 2023-04-03 | |||
4 | 20 | 4 | 2023-04-04 | |||
5 | 25 | 2 | 2023-04-05 | |||
6 | 30 | 0 | 2023-04-06 |
Синтаксис довольно удобен и прост, об этом я писал ещё во время бета-версии SQL Server 2022, но …
Как быть в более старых версиях SQL Server?
Конечно, подобную задачу приходилось решать и ранее, ещё до того, как SQL Server стал SQL Server, поэтому какие-то варианты были всегда. Одни из них были странные, загадочные и непонятные, с проблемами производительности, а другие ещё хуже. Для себя я выбрал два способа: один, работающий, начиная с SQL Server 2016, другой — с SQL Server 2008. Есть решения и для SQL Server 2000, но сегодня не об этом.
Оба варианта реализуем в виде табличных функций с возможностью генерирования до 4000 значений. Конечно, можно выйти за эти рамки, но превышение 8001 значений влечёт за собой поддержку LOB в первом решении, что может непредсказуемо сказаться на производительности. Второй вариант будет ограничен 4096 значениями, поскольку это наивысшая степень 4 меньшая 8001 (далее вы увидите, почему это важно).
2016+ STRING_SPLIT + REPLICATE
Этот приём появился в моём арсенале сравнительно недавно. Не помню, где впервые увидел его, но он мне нравится своей лаконичностью.
CREATE FUNCTION dbo.GenerateSeries_Split
(
@start int,
@stop int
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT TOP (@stop - @start + 1)
value = ROW_NUMBER() OVER (ORDER BY @@SPID) + @start - 1
FROM STRING_SPLIT(REPLICATE(',', @stop - @start), ',')
ORDER BY value
);Количество значений, которые нам нужно получить, будет равно (stop - start + 1).
С помощью REPLICATE генерируем строку из (stop - start) запятых. Далее разбиваем полученную строку с помощью STRING_SPLIT и получаем нужное количество (stop - start + 1) пустых строк. После этого, используя ROW_NUMBER(), нумеруем строки и получаем последовательность чисел от 1 до (stop - start + 1). Для старта последовательности с нужного значения прибавляем к номеру строки start и вычитаем 1.
Для поддержки больше 8001 значений, можно изменить строку с FROM следующим образом:
FROM STRING_SPLIT(REPLICATE(CONVERT(varchar(max),','), @stop - @start), ',')Но об этом варианте мы сегодня говорить не будем, а рассмотрим первоначальный.
2008+ Перекрёстное соединение CTE (Cross Join)
Данный способ можно использовать в более ранних версиях SQL Server, но он более загадочный. Я помню, как впервые использовал такой приём в этом решении после того, как обнаружил действительно эффективную реализацию STRING_SPLIT для SQL Server 2012 у Джонатана Робертса (Jonathan Roberts).
CREATE FUNCTION dbo.GenerateSeries_CTEs
(
@start int,
@stop int
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
/* could work in 2005 by changing VALUES to a UNION ALL */
WITH n(n) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0)) n(n)),
i4096(n) AS (SELECT 0 FROM n a, n b, n c, n d, n e, n f)
SELECT TOP (@stop - @start + 1)
value = ROW_NUMBER() OVER (ORDER BY @@TRANCOUNT) + @start - 1
FROM i4096
ORDER BY value
);Здесь используются два CTE: первое генерирует четыре строки с помощью конструкции VALUES, второе выполняет декартово произведение этих строк друг с другом столько раз, сколько необходимо для покрытия необходимого диапазона значений (в нашем случае 4000).
Каждое декартово произведение даёт 4^n строк, где n — количество упоминаний таблицы n во FROM. Если указать только одну таблицу (SELECT 0 FROM n a), то будет 4^1 строк. Для двух таблиц будет 4^2, то есть 16. И так далее 4^3 = 64, 4^4 = 256, 4^5 = 1024 и 4^6 = 4096. Я попробую показать это на рисунке:
Если вам нужна поддержка только 256 значений, то вы можете оставить во втором CTE только четыре таблицы:
i256(n) AS (SELECT 0 FROM n a, n b, n c, n d)А если нужно больше 4096 значений, например, 16384, — добавьте таблицу ещё раз:
i16K(n) AS (SELECT 0 FROM n a, n b, n c, n d, n e, n f, n g)Конечно, можно использовать альтернативный синтаксис, но по мне он сложнее и выглядит менее понятно.
i4096(n) AS
(
SELECT 0 FROM n AS n4 CROSS JOIN n AS n16
CROSS JOIN n AS n64 CROSS JOIN n AS n256
CROSS JOIN n AS n1024 CROSS JOIN n AS n4096
/* ... */
)Можно использовать защитное программирование, изменив типы параметров на smallint или tinyint, чтобы избежать сюрпризов от использования слишком больших значений int. При передаче большого диапазона ошибки не будет, но будет сгенерировано только 4096 значений. Имейте в виду, что может потребоваться сгенерировать 100 строк в диапазоне от 2 000 000 000 (start) до 2 000 000 100 (stop), поэтому ограничение параметров, вместо контроля количества генерируемых строк, может быть излишним.
Часто встречается использование рекурсивных CTE. Я люблю рекурсивные CTE, и часто их предлагаю, но в этом контексте они неудобны, если только вам не достаточно 100 значений (например, использовать для дней ежемесячного отчёта). Проблема в том, что для получения более 100 значений потребуется указать хинт MAXRECURSION, но его нельзя поместить внутри функции, и придётся писать в каждом запросе, использующем функцию. Ужасно! Вот вам и инкапсуляция.
А что с производительностью?
Я придумал простейший тест — пагинация.
Примечание: пример надуманный и не надо его рассматривать как хорошее решение пагинации данных.
Создадим простую таблицу с 4 000 строками:
SELECT TOP (4000) rn = IDENTITY(int,1,1),*
INTO dbo.things FROM sys.all_columns;
CREATE UNIQUE CLUSTERED INDEX cix_things ON dbo.things(rn);Затем три хранимые процедуры с разными вариантами пагинации.
Первая из них будет использовать функцию GenerateSeries_Split со STRING_SPLIT:
CREATE OR ALTER PROCEDURE dbo.PaginateCols_Split
@PageSize int = 100,
@PageNum int = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @s int = (@PageNum-1) * @PageSize + 1;
DECLARE @e int = @s + @PageSize - 1;
WITH r(rn) AS
(
SELECT TOP (@PageSize) rn = value
FROM dbo.GenerateSeries_Split(@s, @e)
)
SELECT t.* FROM dbo.things AS t
INNER JOIN r ON t.rn = r.rn;
ENDВторая — функцию GenerateSeries_CTEs с декартовым произведением CTE:
CREATE OR ALTER PROCEDURE dbo.PaginateCols_CTEs
@PageSize int = 100,
@PageNum int = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @s int = (@PageNum-1) * @PageSize + 1;
DECLARE @e int = @s + @PageSize - 1;
WITH r(rn) AS
(
SELECT TOP (@PageSize) rn = value
FROM dbo.GenerateSeries_CTEs(@s, @e)
)
SELECT t.* FROM dbo.things AS t
INNER JOIN r ON t.rn = r.rn;
ENDИ третья — встроенную функцию GENERATE_SERIES:
CREATE OR ALTER PROCEDURE dbo.PaginateCols_GenSeries
@PageSize int = 100,
@PageNum int = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @s int = (@PageNum-1) * @PageSize + 1;
DECLARE @e int = @s + @PageSize - 1;
WITH r(rn) AS
(
SELECT TOP (@PageSize) rn = value
FROM GENERATE_SERIES(@s, @e)
)
SELECT t.* FROM dbo.things AS t
INNER JOIN r ON t.rn = r.rn;
ENDДля запуска данных процедур создадим вспомогательную хранимую процедуру, в которую будем передавать номер страницы @pagenum. Таким образом, сможем протестировать получение страницы в начале, в середине и в конце набора данных (производительность пагинации часто снижается по мере увеличения номера страницы). Вряд ли при одиночном вызове этой хранимой процедуры мы заметим разницу в производительности, но при многократном запуске, надеюсь, разница будет заметна.
CREATE OR ALTER PROCEDURE dbo.PaginateCols_Wrapper
@PageNum int = 1
AS
BEGIN
SET NOCOUNT ON;
EXEC dbo.PaginateCols_Split @PageNum = @PageNum;
EXEC dbo.PaginateCols_CTEs @PageNum = @PageNum;
EXEC dbo.PaginateCols_GenSeries @PageNum = @PageNum;
ENDДля сбора информации о времени выполнения будем использовать Query Store.
ALTER DATABASE GenSeries SET QUERY_STORE
(
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = ALL /* Do not do this in production! */
);Хочу вам напомнить, что не стоит использовать QUERY_CAPTURE_MODE = ALL в продакшене, но при разработке вполне допустимо для уверенности в захвате всех запросов.
Для автоматического запуска я использовал sqlstresscmd, указав параметры запуска в файле GenSeries.json:
{
"CollectIoStats": true,
"CollectTimeStats": true,
"MainDbConnectionInfo":
{
"Database": "GenSeries",
"Login": "sa",
"Password": "$tr0ng_P@$$w0rd",
"Server": "127.0.0.1,2022"
},
"MainQuery": "EXEC dbo.PaginateCols_Wrapper @PageNum = 1;",
"NumIterations": 10000,
"NumThreads": 16,
"ShareDbSettings": true
}Здесь настроен запуск dbo.PaginateCols_Wrapper 10 000 раз в 16-ти потоках. В среднем на запуск у меня уходило около 5 минут.
Запускаем, используя следующую команду:
sqlstresscmd -s ~/Documents/GenSeries.jsonИ получаем среднее время выполнения из Query Store:
SELECT qt.query_sql_text,
avg_duration = AVG(rs.avg_duration/1000.0)
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE N'%dbo.things%'
AND qt.query_sql_text NOT LIKE N'%sys.query_store%'
GROUP BY qt.query_sql_text;Перед очередным запуском sqlstresscmd я очищал данные в Query Store следующей командой:
ALTER DATABASE GenSeries SET QUERY_STORE CLEAR;А также изменял параметр MainQuery соответствующим образом, чтобы запустить тесты для страницы в середине и в конце.
Для строк 1901 – 2000:
"MainQuery": "EXEC dbo.PaginateCols_Wrapper = 20;",Для строк 3901 – 4000:
В итоге получились следующие результаты в миллисекундах (нажмите для увеличения):
В моём эксперименте победил вариант со STRING_SPLIT, но новая встроенная функция GENERATE_SERIES идёт вслед за ней прямо по пятам. Вариант с CTE, несмотря на совместимость с более ранними версиями SQL Server, довольно сильно отстаёт.
Конечно, хотелось бы видеть ровные горизонтальные линии, поскольку производительность пагинации не должна зависеть от номера страницы, но сегодня не об этом. Я планирую вернуться к некоторым своим старым техникам пагинации в одной из будущих статей.
Заключение
Я очень рад появлению функции GENERATE_SERIES, и, надеюсь, что вы сможете опробовать её в деле как можно скорее! Производительность подхода со STRING_SPLIT немного лучше, но оба варианта довольно линейны. При возможности я бы предпочёл использовать новый синтаксис. В масштабах моего эксперимента речь идёт о миллисекундах. Но как всегда, стоит тестировать на вашем конкретном кейсе.
И повторю, этот эксперимент не был предназначен для того, чтобы показать, какой из способов пагинации лучше. Это полностью синтетический пример, в котором одна из таблиц была с непрерывной нумерацией, что и позволило сделать соединение со сгенерированным набором строк. Это была демонстрация вариантов реализации GENERATE_SERIES.
Дополнительные материалы
Для генерации рядов чисел есть множество других вариантов, в том числе от Пола Уайта (Paul White), Ицика Бен-Гана (Itzik Ben-Gan) и др., представленных в челлендже Number series generator challenge. В четвёртой части есть интересное решение от Пола Уайта (dbo.GetNums_SQLkiwi), но оно требует небольшой концентрации внимания и имеет ограничение по версиям (требуется таблица с кластеризованным колоночным индексом). Следует всегда проводить тщательное тестирование этого и других вариантов на ваших данных и рабочей нагрузке, особенно когда важна производительность. Некоторые решения будут доступны только на относительно свежих версиях SQL Server и/или, когда у вас есть достаточная свобода в реализации (например, возможность использования CLR).
В заключение приглашаем на открытый урок 22 августа «Обзор автоматизированных средств миграции с MS SQL Server на PostgreSQL». На нем мы научимся эффективно и безопасно переносить данные и схему с одной платформы на другую, оптимизируя процесс и минимизируя риски. Узнаем о лучших практиках и инструментах, которые помогут успешно осуществить миграцию и избежать потерь данных.
Записаться на урок можно на странице практического курса по миграции с MS SQL Server на PostgreSQL.
