Prepare for the second stage of testing

Typical problems

Now, let us employ the RAND function for our problem, namely, for producing two randomly selected models:

Console

Execute

SELECT TOP 2 model 
FROM Product
ORDER BY RAND(model);

SELECT TOP 2 model FROM Product ORDER BY RAND(model);

    Scheme

We obtain two models ascendingly ordered by their numbers. That's where pseudorandomness shows up. Let's have a look at what kind of random numbers we've got:

Console

Execute

SELECT model, RAND(model) rnd 
FROM Product
ORDER BY rnd;

SELECT model, RAND(model) rnd FROM Product ORDER BY rnd;

    Scheme

For brevity sake, I drop part of them:

I don't know which algorythm is used for random number computing, nevertheless I can state that the RAND function behaves monotonously within this range of model numbers. That is why we failed.

 Электронная документация SQL Server Books OnlineBOL gives an example of generating a sequence of random numbers using the system time in order to dynamically change initial value. Here it is:

Console

Execute

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
 + (DATEPART(ss, GETDATE()) * 1000 )
 + DATEPART(ms, GETDATE()) 
 );

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) );

    Scheme

No scheme

However, in this form, the query can be run only in slow packages, so that its repeated execution be less frequent than the time unit used in the query (milliseconds). The use of this function will obviously not meet the condition. But multiplying the argument by some unique identifier will score a success (this approach was suggested by V.I. Gershovich):

Console

Execute

SELECT model, RAND(model*(DATEPART(mm, GETDATE()) * 100000 )
 + (DATEPART(ss, GETDATE()) * 1000 )
 + DATEPART(ms, GETDATE())
 ) 
FROM Product
ORDER BY model;

SELECT model, RAND(model*(DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) FROM Product ORDER BY model;

    Scheme

But here is another problem, related to the argument of the RAND function being integer. If we exceed the maximum allowable value for the integer (for  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server it makes 2^31-1 or 2 147 483 647), we'll get the following error message:

You can check it by running the above query on the website. The error appears for model numbers exceeding 2000. In such cases a normalizing factor is needed as well, for instance,

CASE 
WHEN model < 2000 
THEN model 
ELSE model/10+model % 10 
END

Here, model % 10 (remainder from division by 10) is incorporated, lest significants digits should be lost; otherwise, for models which numbers differ by less then ten equal values might be produced.

The net solution looks like this (naturally, sorting should be performed by rnd rather then by model which we left for clearness of result).

Solution 8.8.3

Console

Execute

SELECT model, 
 RAND(CASE 
 WHEN model < 2000 
 THEN model 
 ELSE model/10+model % 10 
 END *
 (DATEPART(mm, GETDATE()) * 100000 )
 + (DATEPART(ss, GETDATE()) * 1000 )
 + DATEPART(ms, GETDATE()) 
 ) rnd
FROM Product
ORDER BY model;

SELECT model, RAND(CASE WHEN model < 2000 THEN model ELSE model/10+model % 10 END * (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ) rnd FROM Product ORDER BY model;

    Scheme

And now compare results:

Conclusion. The simpler and more reliable way of solving the problem in question is to use the NEWID() function which guarantees the uniqueness of values. However, these values are not numeric. Therefore, when it is a number that is needed, one should pay attention to the RAND() function.