SQL Server中的伪随机可重复排序(不是NEWID()而不是RAND())

我想以可重复的方式对结果进行随机排序,以实现分页等目的.对于这个NEWID()太随机,因为无法重新获得相同的结果.兰德(种子)的排序将是理想的,因为相同的种子会产生相同的随机集合.不幸的是,Rand()状态会重置每一行,有没有人有解决方案？

declare @seed as int;
set @seed = 1000;

create table temp (
id int,
date datetime)

insert into temp (id, date) values (1,'20090119')
insert into temp (id, date) values (2,'20090118')
insert into temp (id, date) values (3,'20090117')
insert into temp (id, date) values (4,'20090116')
insert into temp (id, date) values (5,'20090115')
insert into temp (id, date) values (6,'20090114')

-- re-seeds for every item
select *, RAND(), RAND(id+@seed) as r from temp order by r
--1 2009-01-19 00:00:00.000 0.277720118060575   0.732224964471124
--2 2009-01-18 00:00:00.000 0.277720118060575   0.732243597442382
--3 2009-01-17 00:00:00.000 0.277720118060575   0.73226223041364
--4 2009-01-16 00:00:00.000 0.277720118060575   0.732280863384898
--5 2009-01-15 00:00:00.000 0.277720118060575   0.732299496356156
--6 2009-01-14 00:00:00.000 0.277720118060575   0.732318129327415
-- Note how the last column is +=~0.00002

drop table temp

-- interestingly this works:
select RAND(@seed), RAND()
--0.732206331499865 0.306382810665955

注意,我尝试过Rand(ID),但事实证明它已被排序.显然Rand(n)

1> ccook..：

建立gkrogers哈希建议这很有效.对性能的任何想法？

declare @seed as int;
set @seed = 10;

create table temp (
id int,
date datetime)

insert into temp (id, date) values (1,'20090119')
insert into temp (id, date) values (2,'20090118')
insert into temp (id, date) values (3,'20090117')
insert into temp (id, date) values (4,'20090116')
insert into temp (id, date) values (5,'20090115')
insert into temp (id, date) values (6,'20090114')

-- re-seeds for every item
select *, HASHBYTES('md5',cast(id+@seed as varchar)) r
from temp order by r
--1 2009-01-19 00:00:00.000 0x6512BD43D9CAA6E02C990B0A82652DCA
--5 2009-01-15 00:00:00.000 0x9BF31C7FF062936A96D3C8BD1F8F2FF3
--4 2009-01-16 00:00:00.000 0xAAB3238922BCC25A6F606EB525FFDC56
--2 2009-01-18 00:00:00.000 0xC20AD4D76FE97759AA27A0C99BFF6710
--3 2009-01-17 00:00:00.000 0xC51CE410C124A10E0DB5E4B97FC2AF39
--6 2009-01-14 00:00:00.000 0xC74D97B01EAE257E44AA9D5BADE97BAF

drop table temp

编辑:注意,如果使用动态SQL,则在查询中使用的@seed声明可以替换为参数或使用常量int.(无需以TSQL方式声明@int)