##### Welcome to our SQL Server blog

*This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.*

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Random Number Generator

Published: Jun 19, 2015

Surprisingly this is something that I come across quite often and it’s not the easiest thing to achieve in SQL Server in certain circumstances… a solid piece of code which generates a random number.

We all know that the

So let’s have a look at the

As you can see, running this 5 times gives us 5 different, and random numbers. All good. From this we can easily get a random number generated between, for example 1 and 100:

However, what happens when we use

Now we’ve got a massive problem. But why?

Well, what happens is that

Hence why I’m always asked how it’s possible to actually produce a random number in a set based resultset.

Well, there is a way… use

This is evaluated for each row and not just each batch. However, there’s a small problem… it’s a GUID:

The advantage is that being a GUID it does mean that the necessary randomness of the output can be almost guaranteed, but we do need it in a numeric format.

So, in order to get a random number out of this we can do the following:

First convert the GUID into a 1 byte binary. This makes the GUID just 1 byte, the same as a tinyint. Therefore this effectively turns the GUID into a number between 0 and 255…

Once done we can now convert this into a decimal which we then use to generate our random number between 1 and 100 (note that this time we use 99 as our feed value instead of 100):

Therefore, for any random set of values between 1 and X you would use the following:

And it’s as simple as that.

We all know that the

`rand()`

function exists within SQL Server and therefore this is what most people choose as their solution. However, this can suddenly become less than useful when dealing with sets rather than singular values.So let’s have a look at the

`rand()`

function:`select rand() as random`

go 5

`select floor(rand()*100)+1 as random`

go 5

`rand()`

in a query?`select floor(rand()*100)+1 as random, name`

from sys.objects

Well, what happens is that

`rand()`

is evaluated just the once per batch and therefore this means that you can obtain a random number, but only the once, therefore in a set based output it becomes rather useless.Hence why I’m always asked how it’s possible to actually produce a random number in a set based resultset.

Well, there is a way… use

`newID()`

instead.This is evaluated for each row and not just each batch. However, there’s a small problem… it’s a GUID:

`select newID() as random, name`

from sys.objects

So, in order to get a random number out of this we can do the following:

First convert the GUID into a 1 byte binary. This makes the GUID just 1 byte, the same as a tinyint. Therefore this effectively turns the GUID into a number between 0 and 255…

`select convert(int, convert(varbinary(1), newID())) as random, name`

from sys.objects

`select floor((convert(int, convert(varbinary(1), newID()))/255.0*99)+1) as random, name`

from sys.objects

`declare @topLimit int = X`

select floor((convert(int, convert(varbinary(1), newID()))/255.0*@topLimit)+1) as random, name

from sys.objects

And it’s as simple as that.

## Your email address will not be published. All fields are mandatory.