MS SQL: генерация псевдослучайных данных с использованием newID(). Возможности и подводные камни

Известно, что встроенная функция newID() широко используется разработчиками не только по прямому назначению — то есть для генерации уникальных первичных ключей, но и в качестве средства для генерации массивов псевдослучайных данных.
В составе встроенных функций, newID() фактически единственная, которая не только non-deterministic, но можно сказать и «super-non-deterministic», т.к. в отличие от всех остальных, она способна выдавать новое значение для каждой новой строки, а не одно и то же для всего батча — что делает ее чрезвычайно полезной для подобной массовой генерации. Кроме newID() этим свойством обладает еще newSequentialID(), однако ее использование где-либо, кроме как в задании дефолтного значения колонок типа uniqueidentifier, запрещено.
За примерами далеко ходить не надо — ниже код:

SELECT TOP 100 ABS(CHECKSUM(NEWID())) % 1000 FROM sysobjects A CROSS JOIN sysobjects B 

или вот этот (если кажется, что checksum — трудоемкая операция):

SELECT TOP 100 ABS(CONVERT(INT, (CONVERT(BINARY(4), (NEWID()))))) % 1000 FROM sysobjects A CROSS JOIN sysobjects B 

Сгенерирует нам таблицу из 100 случайных целых чисел в диапазоне от 0 до 999.

Для плавающих чисел можно использовать свойство функции rand() инициализировать генератор целым числом:

SELECT TOP 100 RAND(CHECKSUM(NEWID())) FROM sysobjects A CROSS JOIN sysobjects B 

В данном случае rand() используется по сути просто как преобразователь диапазона int32 в диапазон [0..1). Статистическая проверка качества распределения этим методом на количестве записей порядка миллиона показывает, что оно не уступает стандартному использованию rand(), инициализированному один раз, и далее используемому в цикле. Поэтому — можете смело использовать.

Еще один интересный вариант — генерация нормально-распределенных данных. Здесь будем использовать метод Бокса-Мюллера:

SELECT TOP 1000       COS(2 * PI() * RAND(BINARY_CHECKSUM(NEWID()))) *       SQRT(-2 * LOG(RAND(BINARY_CHECKSUM(NEWID())))) FROM sysobjects A CROSS JOIN sysobjects B CROSS JOIN sysobjects C 

Желающие могут проверить, что сгенерированное распределение очень близко к нормальному, построив график.

Все это хорошо работает и позволяет очень быстро сгенерировать хоть десяток миллионов записей, не используя решения «в лоб» типа циклов, курсоров, или даже вставки записей по одной в базу из слоя приложения. Нужно только убедиться, что таблицы, которые вы используете как источник строк, имеют достаточную емкость, и либо увеличить количество CROSS JOIN’ов, либо использовать табличные переменные с нужным количеством строк в качестве источника.

Однако, тема не только об этом. В подавляющем большинстве случаев сгенерированные строки материализуются, то есть вставляются в постоянную или временную таблицу, либо в табличную переменную. Если это так, то дальше можно не читать — материализованные данные будут работать отлично. Однако, встречаются случаи, когда вышеуказанные стейтменты используются в подзапросах. И вот здесь появляются труднообъяснимые на первый взгляд особенности поведения SQL engine. Рассмотрим их на примерах, а затем попытаемся проанализировать, почему так происходит, и как с этим бороться:

Для начала просто напишем statement с newID() в subquery и запустим его несколько раз в цикле:

declare @c int = 0 while @c < 5 begin   SELECT *    FROM (       SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX       FROM ( 	SELECT 1 AS ID 	) ROWSRC    ) SUBQ     set @c = @c + 1 end 

Код работает ожидаемо — выдает 5 резалтсетов, в каждом строго одна запись с числом в диапазоне от 0 до 4. Скриншота результатов я не привожу — когда и так все в порядке, смысла в них мало.

Теперь интереснее. Пробуем поджойнить результат из SUBQ на какую-нибудь другую таблицу. Ее можно создать, а можно поджойнить subquery на subquery — результат от этого не изменится. Пишем:

declare @c int = 0 while @c < 5 begin   SELECT *    FROM (       SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX       FROM ( 	SELECT 1 AS ID 	) ROWSRC   ) SUBQ   INNER JOIN ( 	SELECT 0 AS VAL  	UNION ALL SELECT 1  	UNION ALL SELECT 2  	UNION ALL SELECT 3  	UNION ALL SELECT 4   ) NUM ON SUBQ.RNDIDX = NUM.VAL     set @c = @c + 1 end 

Смоторим на скриншот результата выполнения — и медленно сползаем под стул — количество строк в каждом резалтсете не равно строго 1. Где-то пусто (это еще можно хоть как-то объяснить — не сработал INNER JOIN из-за выхода RNDIDX из диапазона [0..4] (что само по себе невероятно!)), а где-то — больше одной (!) записи.

Теперь делаем невинное изменение — меняем INNER на LEFT:

declare @c int = 0 while @c < 5 begin   SELECT *    FROM (       SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX       FROM ( 	SELECT 1 AS ID 	) ROWSRC   ) SUBQ   LEFT JOIN ( 	SELECT 0 AS VAL  	UNION ALL SELECT 1  	UNION ALL SELECT 2  	UNION ALL SELECT 3  	UNION ALL SELECT 4   ) NUM ON SUBQ.RNDIDX = NUM.VAL     set @c = @c + 1 end 

Выполняем — все стало работать правильно (!) — проверерьте плз сами, скриншота для правильной работы я не делал. Заметьте, что поскольку для любого значения RNDIDX из диапазона [0..4], которое способно выдать сабквери SUBQ, всегда есть значение VAL из сабквери NUM, то с точки зрения логики результат LEFT и INNER JOIN должен быть одинаков. Однако по факту это не так!

Еще один тест — возвращаем INNER, но добавляем TOP / ORDER BY в первый сабквери. Зачем — об этом позже, давайте просто попробуем:

declare @c int = 0 while @c < 5 begin    SELECT *    FROM (       SELECT TOP 1 ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX       FROM ( 		SELECT 1 AS ID 		) ROWSRC 	  ORDER BY RNDIDX		   ) SUBQ   INNER JOIN ( 	SELECT 0 AS VAL  	UNION ALL SELECT 1  	UNION ALL SELECT 2  	UNION ALL SELECT 3  	UNION ALL SELECT 4   ) NUM ON SUBQ.RNDIDX = NUM.VAL    set @c = @c + 1 end 

Все опять работает правильно! Мистика!

Погуглив, выясняем, что с подобным поведением периодически сталкиваются SQL-разработчики со всего мира — примеры здесь, или здесь

Люди предполагают, что материализация subquery помогает. Действительно, если переписать пример, выбрав сначала записи в явном виде во временную таблицу, а затем только поджойнив, все работает нормально. Почему же на нормальную работу влияет замена INNER на LEFT, или добавление TOP / ORDER BY там, где это не нужно? Все потому же — в одном случае присутствует материализация результатов subquery, в другом — нет. Нагляднее разницу может показать анализ плана более развернутого случая, например вот этого:

DECLARE @B TABLE (VAL INT) INSERT INTO @B VALUES (0), (1), (2), (3), (4)  SELECT *  FROM (       SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX       FROM @B ) SUBQ INNER JOIN @B B ON SUBQ.RNDIDX = B.VAL  Смотрим естественно, только выборку (query 2), заполнение таблицы @B нам ни к чему: 

Мы видим, что запрос сращивает два потока строк до вычисления значения колонки, зависящей от newID(). Это может происходить потому, что SQL engine считает, что значение, возвращаемое newID(), хоть и non-deterministic, но не изменяется в течение всего батча. Однако, это не так — и скорее всего поэтому запрос работает неправильно. Теперь меняем INNER на LEFT, и смотрим план:

Ага, LEFT JOIN заставил SQL engine выполнить Compute Scalar перед объединением потоков, поэтому наш запрос стал работать правильно.

И наконец, проверим версию с добавлением TOP / ORDER BY:

Собственно, диагноз ясен. MS SQL не учитывает особенности newID(), и соответственно, неправильно строит планы, полагаясь на константное значение, возвращаемое функцией в скоупе батча. На эту особенность есть воркэраунд — заставлять SQL engine любыми способами материализовать выборку, перед тем как ее использовать в зависимых запросах. Каким способом вы будете материализовать — дело ваше, однако лучше всего, наверное, использовать табличные переменные, особенно если размер подвыборки невелик. Иначе результат, мягко говоря, не 100% гарантирован; кроме того, нет никакой гарантии, что однажды вы сами, или кто-нибудь другой не отревьюит код, выкинув «ненужные» TOP / ORDER BY или мудро заменив LEFT на INNER.

Собственно, все. Удачного SQL-программирования!