Случайная запись из таблицы базы данных (T-SQL)

есть ли краткий способ получить случайную запись из таблицы sql server?

Я хотел бы рандомизировать мои данные модульного теста, поэтому ищу простой способ выбрать случайный идентификатор из таблицы. На английском языке выбор будет " выбрать один идентификатор из таблицы, где идентификатор является случайным числом между самым низким идентификатором в таблице и самым высоким идентификатором в таблице."

Я не могу понять, как это сделать, не нужно запускать запрос, проверять значение null, а затем повторный запуск, если значение null.

идеи?

5 63
sql-server tsql random

5 ответов:

есть ли краткий способ получить случайную запись из таблицы sql server?

да

SELECT TOP 1 * FROM table ORDER BY NEWID()

объяснение

A NEWID() генерируется для каждой строки, а затем таблица сортируется по ней. Возвращается первая запись (т. е. запись с "самым низким" GUID).

Примечания

  1. GUID генерируются как псевдослучайные числа начиная с версии четыре:

    версия 4 UUID предназначен для генерации UUID из действительно случайных или псевдо-случайных чисел.

    алгоритм выглядит следующим образом:

    • установите два наиболее значимых бита (биты 6 и 7) clock_seq_hi_and_reserved до нуля и единицы соответственно.
    • установите четыре наиболее значимых бита (биты с 12 по 15) поле time_hi_and_version для 4-битного номера версии из Раздел 4.1.3.
    • установите все остальные биты в случайном порядке (или псевдо-случайно) выбран ценности.

    -универсально уникальный идентификатор (UUID) Urn Namespace-RFC 4122

  2. альтернатива SELECT TOP 1 * FROM table ORDER BY RAND() не будет работать так, как можно было бы подумать. RAND() возвращает одно значение на запрос, таким образом, все строки будут иметь одинаковое значение.

  3. в то время как значения GUID являются псевдослучайными, вам понадобится лучший PRNG для более требовательных приложения.

  4. стандартная производительность составляет менее 10 секунд около 1 000 000 строк - конечно, в зависимости от системы. Обратите внимание, что невозможно попасть в индекс, поэтому производительность будет относительно ограниченной.

115

на больших столах вы также можете использовать TABLESAMPLE для этого, чтобы избежать сканирования всей таблицы. 

SELECT  TOP 1 *
FROM YourTable
TABLESAMPLE (1000 ROWS)
ORDER BY NEWID()

The ORDER BY NEWID по-прежнему требуется, чтобы избежать просто возвращать строки, которые появляются первыми на странице данных.

число для использования должно быть тщательно выбрано для размера и определения таблицы, и вы можете рассмотреть логику повтора, если строка не возвращается. Математика за этим и почему техника не подходит для небольших таблиц является здесь обсуждается

17

также попробуйте свой метод, чтобы получить случайный идентификатор между MIN (Id) и MAX(Id), а затем

SELECT TOP 1 * FROM table WHERE Id >= @yourrandomid

Он всегда будет получать вам одну строку.

5

Если вы хотите выбрать большие данные, лучший способ, что я знаю:

SELECT * FROM Table1
WHERE (ABS(CAST(
    (BINARY_CHECKSUM
    (keycol1, NEWID())) as int))
    % 100) < 10

источник: MSDN

3

Я искал, чтобы улучшить методы, которые я пробовал и наткнулся на этот пост. Я понимаю, что это старый, но этот метод не указан. Я создаю и применяю тестовые данные; это показывает метод для "адреса" в SP, вызываемом с @st (два состояния char)

Create Table ##TmpAddress (id Int Identity(1,1), street VarChar(50), city VarChar(50), st VarChar(2), zip VarChar(5))
Insert Into ##TmpAddress(street, city, st, zip)
Select street, city, st, zip 
From tbl_Address (NOLOCK)
Where st = @st


-- unseeded RAND() will return the same number when called in rapid succession so
-- here, I seed it with a guaranteed different number each time. @@ROWCOUNT is the count from the most recent table operation.

Set @csr = Ceiling(RAND(convert(varbinary, newid())) * @@ROWCOUNT)

Select street, city, st, Right(('00000' + ltrim(zip)),5) As zip
From ##tmpAddress (NOLOCK)
Where id = @csr
0