Select top n random rows from a table in SQL SERVER...

There are many ways that you can use randomly selected rows; they're especially effective when you want 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.

The snag is that SQL doesn't permit the selection of random rows. The good news is that there's a simple trick to getting this functionality to work in SQL.

The solution is based on the uniqueidentifierdata type. Unique identifiers, which are also called 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 uniqueidentifierwhose default value is NewID(), 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 4 Column_Name,NEWID() AS RANDOM from TABLE order by RANDOM

Random Number Generator...

There are many methods to generate random number in SQL Server.

Method 1 : Generate Random Numbers (Int) between Rang

-- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

-- This will create a random number between 1 and 999
SET @Lower = 1 -- The lowest random number
SET @Upper = 999 -- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
Method 2 : Generate Random Float Numbers

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
Method 3 : Random Numbers Quick Scripts

-- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
--random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())Method 4 : Random Numbers (Float, Int) Tables Based with Time

DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum
Method 5 : Random number on a per row basis

-- The distribution is pretty good however there are the occasional peaks.
-- If you want to change the range of values just change the 1000 to the maximum value you want.
-- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber