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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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