This is a quick blog to “document” a T-SQL technique for generating random numbers. I’ve been using this for years, but don’t use it frequently enough to have it fully memorized. So whenever I do need it, I must constantly have to go look up whenever I need to use it.

TL;DR

CHECKSUM(NEWID())

SELECT a bunch of Random Numbers

Let’s say you need to generate random numbers as part of a resultset. Here’s a simple example.

/* Use RAND() */
SELECT TOP 10 

	name,

	RAND()

FROM sys.databases;

Unfortunately, if you use RAND(), you’ll get the same value for each record in the resultset. But what if I needed DIFFERENT random values for each record?

There’s a few different approaches you can take but here’s my favorite that I think is the cleanest:

/* Use CHECKSUM(NEWID()) */
SELECT TOP 10 

	name,

	CHECKSUM(NEWID())

FROM sys.databases;

GO 3  -- to loop the prior batch 3 times

Now we get a different random value per record AND different random values per execution!

At this point, you can use whatever other method to limit it down to values that you might want. I use the modulus % operator regularly for this.

/* Generate a random value between 1 and 10 */
SELECT TOP 10 

	name,

	ABS((CHECKSUM(NEWID()) % 10)) + 1

FROM sys.databases;

GO 2

Notice that I added one additional function to the column definition – ABS(). That’s because as you can see in the prior examples, the raw output of CHECKSUM(NEWID()) will return both positive and negative integer values. So if I wanted only positive values between 1 and 10, using ABS() to get the absolute value is a clean solution.

Hope someone else finds this helpful! Thanks for reading.