Generate Random Records

Generate Random Records

Let's say you have a database of customers and you

initiated a contest wherein you determine the winners

by randomly selecting 5 customers from your database.

How would you go about selecting 5 random records

from your customers table?

NEWID() System Function Is used to generate the

Ramdom Records.

Include the NEWID() system function in the

ORDER BY clause when doing your SELECT statement.

Let's use the Customers table in the Northwind database

. To randomly SELECT 5 customers from the table,

we simply have to add the ORDER BY NEWID()

in the SELECT statement. Since we are only concerned

with 5 records, we also have to include the TOP 5 clause

in the statement. The SELECT statement will look as follows:

SELECT TOP 5 [CustomerID], [CompanyName],
[ContactName] FROM [dbo].[Customers] 

ORDER BY NEWID()

Running this SELECT statement generated the following

5 random Customer records (please note that the results

shown here will not match your results even if the same

query has been executed):

CustomerID CompanyName             ContactName                    

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

WANDK      Die Wandernde Kuh       Rita Müller

BERGS      Berglunds snabbköp      Christina Berglund

ROMEY      Romero y tomillo        Alejandra Camino

OTTIK      Ottilies Käseladen      Henriette Pfalzheim

AROUT      Around the Horn        Thomas Hardy

Running the same query again now yields me the following

output, keeping in mind that the results shown here

will again not be the same results you will get due

to the randomness of the NEWID() system function.

CustomerID CompanyName              ContactName                    

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

SIMOB      Simons bistro          Jytte Petersen

QUEDE      Que Delícia            Bernardo Batista

RICAR      Ricardo Adocicados     Janete Limeira

VINET      Vins et alcools        Paul Henriot

LAMAI      La maison d'Asie      Annette Roulet

RAND() System Function - Will It Work?

The randomness of the result set generated by the

previous SELECT statement was due to the NEWID()

system function. There's another system function

that generates a random value when executed,

which is the RAND() system function. If we substitute

the NEWID() with the RAND() system function,

will it generate random records similar to the above?

Let's put that question to the test by modifying the

SELECT statement above and use RAND()

instead of NEWID().

SELECT TOP 5 [CustomerID], [CompanyName],
[ContactName]  FROM [dbo].[Customers] 

ORDER BY RAND()

Running the above SELECT statement generated the

following result set:

CustomerID CompanyName      ContactName                    

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

ALFKI   Alfreds Futterkiste   Maria Anders

ANATR    Emparedados           Ana Trujillo

ANTON    Antonio  Taquería     Antonio Moreno

AROUT    Around the Horn       Thomas Hardy

BERGS   Berglunds snbköp      Christina Berglund

Running it over and over again generated the same

result set. If the RAND() system function is supposed

to generate random numbers when executed, how come

the result set generated are all the same? To investigate

this further, let's include the result of the RAND()

system function as part of the result set.

SELECT TOP 5 [CustomerID],[CompanyName],
[ContactName] FROM [dbo].[Customers] 

ORDER BY RAND()

Executing this SELECT statement generated the

following result set (please note again that the results

shown here will be different from the results you will get,

particularly to the first column).

CustomerID CompanyName           ContactName

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

 ALFKI      Alfreds Futterkiste   Maria Anders

 ANATR      Emparedados           Ana Trujillo

 ANTON      Antonio Taquería      Antonio Moreno

 AROUT      Around the Horn       Thomas Hardy

 BERGS    Berglunds snabbköp    Christina Berglund