TSQL Pseudo Random text generator

Question

I am doing some performance testing on a SQL sproc and just want to bang out a quick data generator for testing. I am after a simple way to generate a pseudo random (true random not needed in this case) varchar field. Ideas I have so far is having a character definition of valid characters that can be used and then build the string from this definition and use a pseudo random length for length variation with a max/min length defined. 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 sig…

Accepted Answer

I wrote a blog post on this recently.

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 - didn't include the random length thing...

```
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 - needs to be correlated...

```
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;
```

TSQL Pseudo Random text generator

Solution

Problem