How to pick random rows with MSSQL
If you want to select some data at random for testing purposes, you want, each time, to select new data, not the same old values.
Microsoft SQL Server has a function: NEWID() that will generate unique identifiers.
Data used
USE sql911;
go
SELECT TOP 25 num FROM nums ORDER BY num;
go
SELECT MAX(num) AS 'Last number' FROM nums;
go
SELECT COUNT(num) AS 'Number of rows' FROM nums;
go
go
SELECT TOP 25 num FROM nums ORDER BY num;
go
SELECT MAX(num) AS 'Last number' FROM nums;
go
SELECT COUNT(num) AS 'Number of rows' FROM nums;
go
num ----------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 (25 row(s) affected) Last number ----------- 1048577 (1 row(s) affected) Number of rows -------------- 1048577 (1 row(s) affected)
The nums table is a generic table used for numbering, linking, and spelling numbers. This table has 1,048,577 rows.
Random rows
SELECT TOP 10 num AS 'First time 10 nums',
newid() AS 'Unique identifier'
FROM nums ORDER BY newid();
go
newid() AS 'Unique identifier'
FROM nums ORDER BY newid();
go
First time 10 nums Unique identifier ------------------ ------------------------------------ 323420 EF6B5F27-B17D-4EE1-9CCD-00000015AC61 648924 C6EC099A-08F4-49C6-9D5D-00002430227B 354462 54757DB5-CF5D-4C2A-B081-000029A5C501 1007878 65E0F9FC-4BC7-4523-9E6A-00002F39D63E 569815 E5A5B37D-BDAF-4274-9839-00004BBAA26B 61390 370E40E2-0240-40F0-AA19-00005252001D 1026247 D7F75210-16B6-4D37-81BE-00005C708979 625188 23603E70-9E34-406B-8B45-00007A2BCD40 599036 EFC72738-BA71-4576-A0E8-0000A60F68F8 324073 2D988A4B-0B16-49AC-8BD1-0000AF86F516 (10 row(s) affected)
SELECT TOP 10 num AS 'Second time 10 nums',
newid() AS 'Unique identifier'
FROM nums ORDER BY newid();
go
newid() AS 'Unique identifier'
FROM nums ORDER BY newid();
go
Second time 10 nums Unique identifier ------------------- ------------------------------------ 867538 98BA79F4-58D8-41DD-A1D1-0000051E3738 856180 875D7BE5-0DEF-44E1-AFC7-000037F664D0 176514 C9FF69D3-250A-4BB0-8837-0000381E4F85 850806 795FEE95-901E-4A5B-A54D-0000383776EC 961598 4CB3AEEC-BD37-4038-A202-0000562376F0 274076 1DFDB6B9-214B-4603-9BC2-00006C0E4975 517961 0D7B0731-0029-4802-8422-0000732DEB57 300232 8A66B202-C7E8-49D7-82E7-000075F57B72 893294 0E3F691C-1E2D-49BA-8B8E-00007D2865C8 893522 01578031-5AC9-4BDD-B046-000091429B09 (10 row(s) affected)
- Notice that Microsoft SQL Server generated different unique IDs.
- These ID are supposed to be unique per server.
- NewID is of a datatype: uniqueidentifier