-- Caution:
/*
        The effectiveness of this solution depends on the values
        of the IDENTITY column & the expression that you choose
        for RAND function. In some cases, it might not be possible
        to ensure a random pattern. If so, then use a procedural
        loop instead.
*/
-- SQL92  Syntax:
SET ROWCOUNT 5;
SELECT O.OrderID , o.CustomerID ,
       RAND( ( @@IDLE % O.IDENTITYCOL )  +
                DATEPART( ms , CURRENT_TIMESTAMP ) +
                O.IDENTITYCOL ) AS RandVal
FROM Northwind..Orders AS O
ORDER BY RandVal;
-- First Run:
/*
OrderID     CustomerID RandVal                                               
----------- ---------- ---------------------
      10754 MAGAA      5.5306126939700005E-5 
      10808 OLDWO      3.5343366707090004E-4 
      10569 RATTC      3.7206663832910003E-4 
      10595 ERNSH      6.1429526468570006E-4 
      11058 BLAUS      7.6335903475130006E-4 
*/
-- Second Run:
/*
OrderID     CustomerID RandVal                                               
----------- ---------- ----------------------------------------------------- 
      10727 REGGC      3.1616772455450001E-4 
      10466 COMMI      3.5343366707090004E-4 
      10621 ISLAT      7.2609309223490009E-4 
      10835 ALFKI      9.3105577607510009E-4 
      10365 ANTON      0.0013037152012391 
*/

-- SQL70 example using TOP clause:
SELECT TOP 5 O.OrderID , o.CustomerID ,
       RAND( ( @@IDLE % O.IDENTITYCOL )  +
                DATEPART( ms , CURRENT_TIMESTAMP ) +
                O.IDENTITYCOL ) AS RandVal
FROM Northwind..Orders AS O
ORDER BY RandVal;
-- First Run:
/*
OrderID     CustomerID RandVal                                               
----------- ---------- ---------------------
      10984 SAVEA      0.0001857369257471 
      10956 BLAUS      5.5839635091110005E-4 
      10873 WILMK      5.5839635091110005E-4 
      11012 FRANK      8.7515686230050008E-4 
      10579 LETSS      0.0016018427413703 
*/
-- Second Run:
/*
OrderID     CustomerID RandVal                                               
----------- ---------- ---------------------
      10579 LETSS      5.0249743713650004E-4 
      10684 OTTIK      5.2113040839470008E-4 
      10276 TORTU      6.7019417846030007E-4 
      10476 HILAA      8.1925794852590007E-4 
      10928 GALED      8.9378983355870001E-4 
*/

-- SQL99 Syntax:
-- This allows you to order by an expression / column not present
-- in the SELECT list. Listed just for completeness.
SET ROWCOUNT 5;
SELECT O.OrderID , o.CustomerID
FROM Northwind..Orders AS O
ORDER BY RAND( ( @@IDLE % O.IDENTITYCOL )  +
                DATEPART( ms , CURRENT_TIMESTAMP ) +
                O.IDENTITYCOL );

This page was last updated on May 01, 2006 04:28 PM.