Mocking
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
Name/Link | ¶ | Date |
---|---|---|
Mock object (Wikipedia) | ¶ | Pending |
Random VIN Generator | ¶ | 2014-11 |