Getting Random Rows, Random Numbers with Sql Server

{

The 5 second version of this, should you arrive via search, is that to get random rows, simply use an TOP query with  ORDER BY NewId() expression. It’s really that simple! Take a look:

-- SAMPLE TABLE
CREATE TABLE Keywords(
	KeywordId INT IDENTITY(1,1) PRIMARY KEY, 
	KeywordValue VARCHAR(50)
)
GO

-- SAMPLE DATA
DECLARE @N INT
SET @N = 1
WHILE @N < 101 BEGIN
	INSERT INTO Keywords(KeywordValue)
		VALUES('key word ' + CONVERT(VARCHAR(5), @N))
	SET @N = @N + 1
END

-- A TOP QUERY WITH ORDER BY NEWID()
SELECT TOP 5 * FROM Keywords ORDER BY NEWID()

The longer version of this is that I recently was asked to generate random keywords for a website I was working on with a lookup table. Getting random numbers in TSQL is easy, the RAND() function does all the magic but getting rows is a lot more tricky, especially if you want to make sure you exclude anything you’ve previously retrieved. The above technique worked quite well and made it easy to allow for them to add and remove keywords on demand.

One interesting application of this is that you can combine it with RAND() to get random numbers via multiplier and random rows (RAND gets a random between 0 and 1 leaving you the responsibility to multiply it to control the range you want).

Here is where I thought this could be taken as a flexible way to get random numbers within a bounded range:

-- get a series of random numbers
SELECT TOP 5 Rand() * KeywordId FROM Keywords ORDER BY NEWID()

There’s a lot of flexibility but SQL Server makes it trivial. 

}