Проблема:

В своей работе мне часто приходится заменять конфиденциальную информацию в базах данных SQL Server случайными строками. Это особенно полезно при восстановлении производственной базы данных в тестовую среду или при генерации фиктивных данных для разработки. У меня есть определенные требования к генерации случайных строк:

• Я должен иметь возможность определить длину строки в заданном диапазоне.
• Я хочу иметь возможность генерировать точно такие же строки повторно при необходимости, чтобы обеспечить одинаковое количество и качество данных.
• Я хочу иметь возможность генерировать случайные строки с простыми шаблонами, такими как формат почтового индекса в Канаде.

Решение:

Существует несколько способов генерации случайных строк в T-SQL, таких как использование функций Rand(), NewID(), CRYPT_GEN_RANDOM() и функций Convert/Cast/Substring. Однако использование чистого T-SQL имеет некоторые недостатки:

1. Недетерминированные функции, такие как Rand(), NewID() и CRYPT_GEN_RANDOM(), не могут использоваться внутри UDF (User-Defined Function), требуя дополнительного уровня для обхода этого ограничения.
2. Производительность чистых T-SQL решений компрометируется при генерации большого количества строк.

Для преодоления этих ограничений можно использовать функцию CLR (Common Language Runtime). Функции CLR лучше всего подходят для манипулирования строками внутри SQL Server. Здесь я предоставлю две функции CLR, которые удовлетворяют вышеупомянутым требованиям:

1. fn_random_string: Эта функция генерирует случайную строку с указанной длиной и параметром seed, обеспечивая повторяемость с той же случайной строкой при использовании того же seed.
2. fn_random_pattern: Эта функция генерирует случайную строку с простым шаблоном, определенным параметром pattern. Она также имеет параметр seed для обеспечения повторяемости генерации строк.

Вот пример того, как создать и использовать эти функции CLR:

CREATE ASSEMBLY clr_random_string
FROM 'C:\mssqltips\Random_String\bin\CLR_Rand_String.dll'
WITH PERMISSION_SET = SAFE;

CREATE FUNCTION dbo.ucf_random_string (@minLen INT, @maxLen INT, @seed INT = 0)
RETURNS NVARCHAR(MAX) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [clr_random_string].[UserDefinedFunctions].fn_random_string;

CREATE FUNCTION dbo.ucf_random_pattern (@pattern NVARCHAR(MAX), @seed INT = 0)
RETURNS NVARCHAR(MAX) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [clr_random_string].[UserDefinedFunctions].fn_random_pattern;

-- Сгенерировать одну случайную строку
SELECT RandStr = dbo.ucf_random_string(10, 30, DEFAULT),
       RandPattern = dbo.ucf_random_pattern('What is the time, Mr. @@@@@@? It is ! am', DEFAULT);

-- Сгенерировать случайный почтовый индекс Канады / почтовый индекс США
SELECT TOP 10 Canada_PostCode = UPPER(dbo.ucf_random_pattern('@!@ !@!', ROW_NUMBER() OVER (ORDER BY column_id))),
             US_ZipCode = dbo.ucf_random_pattern('!!!!!-!!!!', CEILING(RAND(column_id)) + ROW_NUMBER() OVER (ORDER BY column_id))
FROM sys.all_columns;

Сравнение производительности между CLR и T-SQL:

Важно сравнить производительность решений CLR и T-SQL. Вот пример того, как протестировать производительность:

DECLARE @i INT = 1, @start_time DATETIME = GETDATE();
DECLARE @str VARCHAR(8000), @seed INT;
DECLARE @t_tsql TABLE (run_count INT, duration_ms INT); -- для статистики выполнения T-SQL
DECLARE @t_clr TABLE (run_count INT, duration_ms INT); -- для статистики выполнения CLR

-- Выполнить решение T-SQL 20 000 раз
WHILE @i <= 20000
BEGIN
    SET @seed = @i;
    EXEC dbo.usp_generateIdentifier
        @minLen = 2000,
        @maxLen = 4000,
        @seed = @seed OUTPUT,
        @string = @str OUTPUT;

    IF (@i % 2000 = 0)
        INSERT INTO @t_tsql (run_count, duration_ms)
        SELECT @i, DATEDIFF(ms, @start_time, GETDATE());

    SET @i = @i + 1;
END

SET @i = 1;
SET @start_time = GETDATE();

-- Выполнить решение CLR 20 000 раз
WHILE @i <= 20000
BEGIN
    SET @seed = @i;
    SELECT @str = dbo.ucf_random_string(2000, 4000, @seed);

    IF (@i % 2000 = 0)
        INSERT INTO @t_clr (run_count, duration_ms)
        SELECT @i, DATEDIFF(ms, @start_time, GETDATE());

    SET @i = @i + 1;
END

SELECT t1.run_count, tsql_duration = t1.duration_ms, clr_duration = t2.duration_ms
FROM @t_tsql t1
INNER JOIN @t_clr t2 ON t1.run_count = t2.run_count;

Вывод:

Генерация случайных строк в SQL Server может быть достигнута с использованием функций CLR, которые обеспечивают лучшую производительность и гибкость по сравнению с чистыми решениями T-SQL. Используя функции CLR, вы легко можете генерировать случайные строки с определенной длиной и шаблонами, обеспечивая повторяемость при необходимости.