数据库表中的随机记录(T-SQL) [英] Random record from a database table (T-SQL)

问题描述

是否有一种简洁的方法可以从sql server表中检索随机记录?

Is there a succinct way to retrieve a random record from a sql server table?

我想将我的单元测试数据随机化，所以我正在寻找一种从表中选择随机ID的简单方法.用英语来说，选择将是从表中选择一个ID，其中ID是表中最低ID和表中最高ID之间的随机数".

I would like to randomize my unit test data, so am looking for a simple way to select a random id from a table. In English, the select would be "Select one id from the table where the id is a random number between the lowest id in the table and the highest id in the table."

我无法找到一种方法来执行此操作，而不必运行查询，测试null值，然后在null时重新运行.

I can't figure out a way to do it without have to run the query, test for a null value, then re-run if null.

想法?

推荐答案

是否有一种简洁的方法可以从sql server表中检索随机记录?

Is there a succinct way to retrieve a random record from a sql server table?

是

SELECT TOP 1 * FROM table ORDER BY NEWID()

说明

为每一行生成一个NEWID()，然后对该表进行排序.返回第一个记录(即具有最低" GUID的记录).

Explanation

A NEWID() is generated for each row and the table is then sorted by it. The first record is returned (i.e. the record with the "lowest" GUID).

自第四版以来，

1. GUID作为伪随机数生成:

1. GUIDs are generated as pseudo-random numbers since version four:

版本4 UUID用于根据真正的随机或 伪随机数.

The version 4 UUID is meant for generating UUIDs from truly-random or pseudo-random numbers.

算法如下:

• 设置最高有效位的两个最高位(第6位和第7位) clock_seq_hi_and_reserved分别为零和一.
• 设置地址的四个最高有效位(第12至15位) time_hi_and_version字段到从的4位版本号 第4.1.3节
• 将所有其他位设置为随机(或伪随机)选择 值.

• Set the two most significant bits (bits 6 and 7) of the clock_seq_hi_and_reserved to zero and one, respectively.
• Set the four most significant bits (bits 12 through 15) of the time_hi_and_version field to the 4-bit version number from Section 4.1.3.
• Set all the other bits to randomly (or pseudo-randomly) chosen values.

— 通用唯一标识符(UUID)URN命名空间-RFC 4122

替代项SELECT TOP 1 * FROM table ORDER BY RAND()不会像人们想象的那样起作用. RAND()每个查询返回一个单一值，因此所有行将共享相同的值.

The alternative SELECT TOP 1 * FROM table ORDER BY RAND() will not work as one would think. RAND() returns one single value per query, thus all rows will share the same value.

尽管GUID值是伪随机的，但对于要求更高的应用程序，您将需要更好的PRNG.

While GUID values are pseudo-random, you will need a better PRNG for the more demanding applications.

大约1,000,000行的典型性能不到10秒—当然取决于系统.请注意，不可能达到索引，因此性能将受到相对限制.

Typical performance is less than 10 seconds for around 1,000,000 rows — of course depending on the system. Note that it's impossible to hit an index, thus performance will be relatively limited.

这篇关于数据库表中的随机记录(T-SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！