DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to get random results from SELECT?

Execute the Microsoft SQL Server T-SQL code example to demonstrate random sampling of the Product table:

-- T-SQL random sampling with NEWID and TOP functions

SELECT TOP (7)

       ProductID, ProductName=Name, ProductNumber,

       Color, ListPrice

FROM AdventureWorks2008.Production.Product

WHERE ListPrice > 0 and Color is not null

ORDER BY NEWID()

/*

PID   ProductName                   ProdNo      Color       ListPrice

849   Men's Sports Shorts, M        SH-M897-M   Black       59.99

792   Road-250 Red, 58              BK-R89R-58  Red         2443.35

828   HL Road Rear Wheel            RW-R820     Black       357.06

884   Short-Sleeve Jersey,    XL    SJ-0194-X   Yellow      53.99

840   HL Road Frame - Black, 52     FR-R92B-52  Black       1431.50

720   HL Road Frame - Red, 52       FR-R92R-52  Red         1431.50

799   Road-550-W Yellow, 42         BK-R64Y-42  Yellow      1120.49

*/

 

------------

-- Generating Random Numbers with NEWID() & CHECKSUM()

------------

SELECT Random=CONVERT(VARCHAR,ABS(CHECKSUM(NEWID())))

FROM master.dbo.spt_values

ORDER BY Random;

/*

Random

1000945892

1003905302

1008347353  ..... */

------------

Related article:

NEWID (Transact-SQL)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE