Prepare for the second stage of testing

Typical problems

In due time we used random sampling of entries to create a list of questions for a test. We did it on the client with the help of basic language functions which generate pseudorandom numbers within a given range (for example, the RND function in Visual Basic).

It turned out, however, quite simple to arrange it on the server side. Both by similar tools (with the help of the RAND function in T-SQL) and on the basis of uniqueidentifier datatype which is called global unique udentifier and lools like:

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,

For the solution of our problem it is essential that this unique identifier can be automatically generated by employing the NEWID function. Just write down in QA (Query Analyzer) or in MS (Management Studio)

Console

Execute

SELECT NEWID();

SELECT NEWID();

    Scheme

No scheme

or

Console

Execute

SELECT TOP 1 NEWID() FROM PC

SELECT TOP 1 NEWID() FROM PC

    Scheme

So, the idea is to add a computed column NEWID, by which the sorting should be performed, to the query yielding the required number of entries. Since generated values are arbitrary, we'll get a random fetch.

Assume that we need to select two random models from the Product table:

Solution 8.8.1

Console

Execute

SELECT model 
FROM (SELECT TOP 2 model, NEWID() [id] 
 FROM Product 
 ORDER BY [id]
 ) x;

SELECT model FROM (SELECT TOP 2 model, NEWID() [id] FROM Product ORDER BY [id] ) x;

    Scheme

Siolution 8.8.2

Console

Execute

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

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

    Scheme

Let us run this query several times:

You can carry on if you like.

Now let us come back to the "traditional" way. The RAND function generates pseudorandom floating point numbers between 0 and 1.

The term 'pseudorandom' denotes that such numbers are computed by means of a certain atithmetic algorythm. That is, with the initial conditions (input) being equal, the derived numbers will be the same. The initial conditions may be set explicitly, by supplying the function argument which can be any number of tinyint, int or smallint type, or implicitly. In the last case the argument is omitted and the initial value is selected by  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.

Let us execute the following query:

Console

Execute

SELECT TOP 1 RAND(), RAND(350) 
FROM Product;

SELECT TOP 1 RAND(), RAND(350) FROM Product;

    Scheme

We will run it in MS. I got 0.0485421339242268 and 0.72009490018203537. It may be safely stated that the first number you get will be different, but the second may be the same, since in this case the initial value was specified (350).