TSQL伪随机文本生成器

问题描述

我正在对SQL sproc进行一些性能测试，只想敲出一个快速的数据生成器进行测试.

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

我正在寻找一种生成伪随机(在这种情况下不需要真正的随机)varchar字段的简单方法.

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.

我的测试数据生成器:

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

推荐答案

我最近对此写了一篇博客文章.

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('')
;

对不起-不包括随机长度的东西...

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? */

对不起-需要关联...

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伪随机文本生成器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！