Terminology

Facts, Thoughts and Opinions

Randomizing and Shuffling Data in SQL Server

The intuitive manner is to use RAND(), like so:

SELECT ROW_NUMBER() OVER(ORDER BY RAND()) AS [RowNumber], <otherFieldsFromTable>
FROM <someTableOrView>

However, this doesn't work because RAND() is evaluated only once per column. TO make this work, use NEWID() instead of RAND().

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

One could randomize data in this manner:

CROSS JOIN (
    SELECT COUNT(*) AS [count] FROM <tableOrView>
) <alias>Count
LEFT OUTER JOIN (
    SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) AS [RowNumber], <fields>
    FROM <tableOrView>
) <alias> ON <alias>.[RowNumber] = n.Number % <alias>Count.[count] + 1

However, NEWID() is an expensive option. If the row has an ID, you can generate one random number and use it as a modulus:

SELECT TOP 500 * FROM <tableOrView> ORDER BY [ID] % CAST(RAND()*400 AS INT)

Get a random date up to one year in the future:

SELECT DATEADD(DD, DATEDIFF(DD,0, GETDATE())+ ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 365, 0) AS [Date]

Images

[[/div]]
  •   Subtopics

  •   Writings

  Sources & Bookmarks