TSQL Pseudo Random Text Generator

Question

I am doing some performance tests on SQL sproc and just want to release a quick data generator for testing.

I get an easy way to generate a pseudo-random (true random, not required in this case) varchar field.

The ideas I have so far have a definition of the characters of real characters that can be used, and then build a string from this definition and use a pseudo-random length to change the length with a given maximum length / min.

Edit:

My test data generator:

DECLARE @MyDataTable TABLE ( RecID int IDENTITY(1,1) PRIMARY KEY, SomeText varchar(255) ) DECLARE @RecId int, @SomeText varchar(255), @maxlength int, @minlength int, @RecordCount int, @Counter int SET @maxlength = 254 SET @minlength = 50 SET @RecordCount = 500000 SET @Counter = 1 WHILE (@Counter < @RecordCount) BEGIN INSERT INTO @MyDataTable ( SomeText ) SELECT TOP 1 ( select top (abs(checksum(newid())) % (@ maxlength-@minlength ) + @minlength) char(abs(checksum(newid())) % 26 + ascii('A')) from sys.all_objects a1 where sign(a1.object_id) = sign(t.object_id) /* Meaningless thing to force correlation */ for xml path('') ) as NewRandomString FROM sys.all_objects t; SET @Counter = @Counter + 1 END 

Answer 1

Recently, I wrote a blog post.

http://msmvps.com/blogs/robfarley/archive/2009/12/07/randomising-data.aspx

 select top (@stringlength) char(abs(checksum(newid())) % 26 + ascii('A')) from sys.all_objects for xml path('') ; 

Edit: Sorry - did not include a random length ...

 SELECT ( select top (abs(checksum(newid())) % (@ maxlength-@minlength ) + @minlength) char(abs(checksum(newid())) % 26 + ascii('A')) from sys.all_objects for xml path('') ) as NewRandomString FROM yourTable; /* Maybe something like dbo.nums? */ 

Edit: Sorry - you need to correlate ...

 SELECT ( select top (abs(checksum(newid())) % (@ maxlength-@minlength ) + @minlength) char(abs(checksum(newid())) % 26 + ascii('A')) from sys.all_objects a1 where sign(a1.object_id) = sign(t.object_id) /* Meaningless thing to force correlation */ for xml path('') ) as NewRandomString ,* FROM sys.all_objects t; 

Answer 2

For SQL Server 2008

 SELECT --fixed length CAST(CRYPT_GEN_RANDOM(50) AS varchar(100)), --variable length CAST(CRYPT_GEN_RANDOM(ABS(CHECKSUM(NEWID()))%50) AS varchar(100)) 

Examples:

 r¡Ñ"ã8Ò¯wß×1W=ýÎÜTÜN:Læ*é=Öô/qAtmտ׌1):¢ìèð'¾N mÁBòºÇòWãmßyWßðÛ2ï¬"œ¹t ¦2›ÏÀë?î7Ä›››ºªb 

My evil double wants to use this as a password generator ...

Answer 3

This will create a random string of variable length.

 DECLARE @text nvarchar(255), @length int, @i int; SET @i = 0 SET @text = '' SET @length = RAND() * 50 + 215 WHILE (@i < @length) BEGIN SET @text = @text + CHAR(RAND() * 26 + 65) SET @i = @i + 1 END 

Answer 4

If you need it quickly or don’t want to do it yourself, you can also use the tool from

http://www.generatedata.com/

but you can only generate 100 lines if you just use an online demo.