How to select records randomely from the SQL Server table.

There are many times when we need to select some records randomely from the SQL Server table.

For that purpose there are many ways that can be utilized for the same. they're especially effective when you wish to add dynamism to a site. For instance, you could randomly select a product to present as Today's Featured Product, or QA could generate a random call list to gauge customer satisfaction levels.

But the snag is that SQL doesn't permit the selection of random rows. then howe to do that ?
But you don't need to worry becaues the good news is that there's a simple trick to getting this functionality to work in SQL.

The solution is based on the uniqueidentifier data type. Unique identifiers, popularily known as Guaranteed Unique Identifiers (GUIDs), look something like this:

4C34AA46-2A5A-4F8C-897F-02354728C7B0

SQL Server uses GUIDs in many contexts, perhaps most notably in replication. You can use them when normal incrementing identity columns won't provide a sufficient range of keys. To do this, you create a column of type uniqueidentifier whose default value is NewID(), something like this:

CREATE TABLE MyNewTable
(
PK uniqueidentifier NOT NULL DEFAULT NewID(),
AnotherColumn varchar(50) NOT NULL,
. . .

This function is just the ticket to solve our random rows problem. We can simply call NewID() as a virtual column in our query, like this:

Select top , NEWID() AS RANDOM from TABLE order by RANDOM

you can now get the number of records selected randomly.

and that's what we want .

so cheers ....