Saturday, March 17, 2007

Returning random records.. MS SQL Server vs. MySQL

Sometimes it's useful to return a number of random records from a database. For example a "tip of the day" functionality or displaying random products in an e-commerce site.
In MS SQL Server, this can be done using a technique that depends on the newid() function that returns a GUID . This technique is well described in this SQLTeam article.
I tried to use the same technique in MySQL using a query like:

select name from country order by uuid() limit 10;

Thinking that the uuid() function will do the same job as newid() in MS SQL Server.
for my astonishment, this could not do the job at all, the same 10 records are always returned.
After changing the query to :

select name,uuid() from country limit 10;

the results where:
| name | uuid() |
| Anguilla | 85f55cb0-2620-102a-9f3b-3e89f326e405 |
| Argentina | 85f55cf4-2620-102a-9f3b-3e89f326e405 |
| Australia | 85f55d0f-2620-102a-9f3b-3e89f326e405 |
| Austria | 85f55d2a-2620-102a-9f3b-3e89f326e405 |
| Belgium | 85f55d64-2620-102a-9f3b-3e89f326e405 |
| Brazil | 85f55d73-2620-102a-9f3b-3e89f326e405 |
| Canada | 85f55d80-2620-102a-9f3b-3e89f326e405 |
| Chile | 85f55d8d-2620-102a-9f3b-3e89f326e405 |
| China | 85f55d9b-2620-102a-9f3b-3e89f326e405 |
| Costa Rica | 85f55da8-2620-102a-9f3b-3e89f326e405 |

Looking at the results above, the uuid() function seems to return sequential unique identifier. which is not the intended behavior by any means (in my case of course).

So..I tried another technique that would fail in SQL Server(As I may explain why later)

select name from country order by rand() limit 10;

and this worked great!! Each time I run this query, different 10 country names are returned.

Why does using rand() to get random records in SQL Server fail? And how to overcome this?

Ok, maybe I'll talk about this later in another post. I want to sleep.

No comments: