Można sobie wyobrazić przynajmniej kilka sytuacji, w których potrzebować będziemy losowych (w zasadzie - pseudolosowych) danych, np. testy wydajnościowe, różnego typu badania silników bazodanowych, testy lub prezentacje aplikacji itd. Oczywiście generowanie takich danych może być przeprowadzone na szereg sposobów, włączając w to zastosowanie integracji CLR i zewnętrznych skryptów lub programów wykonanych w różnych technologiach. Dane losowe mogą powstawać na podstawie odpowiednich zbiorów słownikowych (np. osobowe dane teleadresowe ze słowników ulic, miast, imion, nazwisk itd.) lub praktycznie "od zera" - znak po znaku. Poniżej zaprezentuję generowanie danych losowych bez żadnych zesłownikowanych wzorców za pomocą czystego T-SQL.
TrochÄ™ teorii
SQL Server dostarcza kilku funkcji, które potencjalnie mogą być zastosowane do zrealizowania zadania. Należą do nich:
- NEWID(),
- NEWSEQUENTIALID(),
- CHECKSUM(),
- RAND().
1. NEWID() i NEWSEQUENTIALID()
Funkcja NEWID() zwraca wartość typu uniqueidentifier, czyli losowy 16 bajtowy globalny unikalny identyfikator (tzw. GUID -
Global Unique Identifier). Nie wdając się w szczegółowe postanowienia standardów, można przyjąć, że globalny unikalny identyfikator nie zostanie nigdy wygenerowany ponownie (także przez żaden inny komputer). Kolejną interesującą funkcją jest NEWSEQUENTIALID(), która także potrafi generować wartości typu uniqueidentifier. Może być ona wykorzystana wyłącznie jako wartość DEFAULT kolumny i w odróżnieniu od NEWID() - działa sekwencyjne (następna wartość jest więc łatwo przewidywalna). Ze względu na specyfikę działania, NEWSEQUENTIALID() nie gwarantuje, że każda wygenerowana wartość będzie unikalna globalnie - gwarantuje natomiast unikalność lokalną (na danym komputerze).
2. CHECKSUM()
CHECKSUM() jest słabą funkcją skrótu stosowaną przez SQL Server. Ilość kolizji i stosunkowo wysokie prawdopodobieństwo, że mimo zmiany strumienia wejściowego zwrócony zostanie identyczny hash sprawiają, że według mnie zupełnie nie nadaje się ona do poważniejszych zastosowań. W zupełności natomiast wystarczy podczas generowania losowych danych. A tak btw, zabawnie brzmi niezmiernie precyzyjne stwierdzenie w BOL:
However, there is a small chance that the checksum will not change (when the input changes - M.G.). Jak mała jest
small chance? PozostawiÄ™ to bez komentarza.
3. RAND()
RAND() teoretycznie zwraca pseudolosową wartość typu float z zakresu 0..1. W praktyce jednak rozkład jest na tyle żałosny, że IMHO nawet do pseudolosowości dużo tej funkcji brakuje. Wystarczy popatrzeć na wynik takiego zapytania:
SELECT RAND()
UNION SELECT RAND()
UNION SELECT RAND()
UNION SELECT RAND()
UNION SELECT RAND()
UNION SELECT RAND()
UNION SELECT RAND()
UNION SELECT RAND()
UNION SELECT RAND()
UNION SELECT RAND();
-- rozkład powinien być o wiele bardziej równomierny, wartości nie powinny koncentrować się w żadnym
-- przediale - w poniższym przykładzie 60% wartości przypada na przedział 0.61 - 0.74
0,0848286198290297
0,104381141303904
0,508508705889991
0,618098453236271
0,653159245956311
0,662219841853496
0,693312607510701
0,73525260716506
0,736247814304469
0,872693982944429
Funkcja zwróci tę samą wartość w całym zbiorze (to działanie zgodne z założeniami):
SELECT RAND() FROM sys.objects;
Żeby jej wynik był różny dla każdego wiersza, trzeba dostarczyć jej odpowiednie ziarno (ang.
seed). Jeżeli ziarno nie będzie dostatecznie długie i dostatecznie losowe, także RAND() przestanie się zachowywać jak funkcja typu random:
SELECT RAND(x.id) FROM
(
SELECT 2 as id
UNION ALL
SELECT 5
UNION ALL
SELECT 56
) x;
-- wynik o mało losowym rozkładzie (delikatnie mówiąc)
0,713610626184182
0,713666525097956
0,714616806632125
Oczywiście można do funkcji RAND() dostarczyć odpowiednio zrzutowaną wartość NEWID() i wtedy rozkład będzie akceptowalny - tylko po co w takiej sytuacji wykorzystywać RAND(), skoro można bezpośrednio użyć NEWID().
Generowanie danych różnych typów
Wymienione funkcje można wykorzystać na bardzo różne sposoby dostosowując obsługę do swoich potrzeb. Zachęcam do kreatywności :) Poniżej przedstawię kilka metod, które najczęściej wykorzystuję.
1. Int i numeric
Do wygenerowania pojedynczych danych numerycznych można wykorzystać funkcję RAND(), ewentualnie mnożąc jej wynik przed odpowiednią liczbę:
SELECT RAND()*5000;
Jeżeli chcemy uzyskać wartość INT, wystarczy zaokrąglić wynik:
SELECT ROUND(RAND()*100000,0);
Alternatywą lepiej sprawdzającą się w realiach zbiorów jest zrzutowanie wyniku funkcji NEWID() na typ int (nie jest to jednak dopuszczalne bezpośrednio - trzeba więc zastosować pośrednie rzutowanie na typ varbinary):
SELECT CAST(CAST(NEWID() as varbinary(16)) as int);
2. Dane tekstowe
Jeżeli nie zależy nam na treści, a jedynie na odpowiednim typie (np. nvarchar), wystarczy zrzutować NEWID() na ten typ znakowy, np.:
SELECT CAST(NEWID() as nvarchar(36));
Jeżeli chcemy uzyskać tekst bardziej różnorodny, ale nie zależy nam na wrażeniach estetycznych i nie przeszkadzają nam znaki nie mające nic wspólnego z alfabetem, możemy wykorzystać:
SELECT CAST(CAST(NEWID() as varbinary(16)) as varchar(16));
Jeżeli takie znaki nam przeszkadzają, to pozostaje np. wykorzystać funkcję CHAR() i dostarczyć jej wartości pochodzące z odpowiednio ograniczanego wyniku funkcji RAND().
Wydajne generowanie danych
Wszystkie wyżej wymienione metody mają porównywalną wydajność. Naturalnie najczęściej jednostkowe generowanie pseudolosowej wartości trzeba powtórzyć określoną liczbę razy, aby powstał wynikowy zbiór składający się z żądanej liczby wierszy. Można do tego wykorzystać tzw. execution loop (więcej informacji:
tutaj) lub standardową pętle WHILE. W sytuacji dodawania odpowiednio generowanych rekordów do tabeli, warto całą operację objąć jedną transakcją (więcej informacji:
tutaj). Dodatkowo przy tego typu operacjach należy wyłączyć drukowanie komunikatów rows affected (opcja sesji NOCOUNT). Przykład:
SET NOCOUNT ON;
GO
CREATE TABLE random_data
(
GUIDcol uniqueidentifier
);
GO
-- pętla WHILE - niewydajnie (17 sek.)
DECLARE @i int;
SET @i =0;
WHILE(@i < 50000)
BEGIN
INSERT INTO random_data(GUIDcol) SELECT NEWID();
SET @i = @i +1;
END
TRUNCATE TABLE random_data;
GO
-- pętla WHILE - wydajnie (1 sek., 17 razy szybciej)
DECLARE @j int;
SET @j =0;
BEGIN TRAN;
WHILE(@j < 50000)
BEGIN
INSERT INTO random_data(GUIDcol) SELECT NEWID();
SET @j = @j +1;
END
COMMIT TRAN;
TRUNCATE TABLE random_data;
GO
-- execution loop - niewydajnie (24 sek.)
INSERT INTO random_data(GUIDcol) SELECT NEWID();
GO 50000
TRUNCATE TABLE random_data;
GO
-- execution loop - wydajnie (4 sek., 6 razy szybciej)
BEGIN TRAN;
GO
INSERT INTO random_data(GUIDcol) SELECT NEWID();
GO 50000
COMMIT TRAN;
-- DROP TABLE random_data;
Warto pamiętać o powyższych kwestiach związanych z wydajnością, gdyż wielokrotnie przyspieszają generowanie danych, a co istotniejsze - są znacznie mniej obciążające dla loga transakcyjnego, czyli de facto dla całego serwera bazodanowego.
