[原创] 生成X个不重复的Y位长度的随机数

/*
 [原创] 生成X个不重复的Y位长度的随机数
   by cuixiping(无心) 2007-04-13
 (经测试，速度还可以。改成存储过程也很容易，我就偷懒不写了。)

看到邹建大哥有一篇：
    如何快速生成100万不重复的8位编号
    http://blog.csdn.net/zjcxc/archive/2006/08/20/1099215.aspx
我是用另一种思路写的。
*/
declare @X int;  /* 要获取的个数 */
declare @Y tinyint;  /* 要获取的长度，越大越慢 */
declare @i int;
declare @j bigint;
declare @SQL1 varchar(1000);
declare @SQL2 varchar(1000);
declare @SQL3 nvarchar(2000);
set @X=100;
set @Y=6;   /* 10的@Y次方必须大于@X。且@Y不要太大，否则会很慢，量“机”而为 */
set @i=2;
set @j=10;

USE tempdb
-- 创建测试表及其数据
if exists (select 1 from  sysobjects  where  name = 'T' and type = 'U')
   drop table T;
CREATE TABLE T(n tinyint);
INSERT INTO T SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 0
;
-- 组装语句
set @SQL1='select top '+ CAST(@X AS varchar) +' t1.n * '+ CAST(POWER(@j,@Y-1) AS varchar);
set @SQL2=' as nn from T t1';
WHILE @i<=@Y
BEGIN
   set @SQL1 = @SQL1 + '+t' + CAST(@i AS varchar) + '.n*'+ CAST(POWER(@j,@Y-@i) AS varchar);
   set @SQL2 = @SQL2 + ',T t' + CAST(@i AS varchar);
   set @i=@i+1;
END
set @SQL3 = @SQL1 + @SQL2 + ' where t1.n>0 order by newid()';
-- 执行语句
EXEC sp_executesql @SQL3;
-- 删除测试
DROP TABLE T;