I used this query:

SELECT FLOOR(3 * RAND(CONVERT(varbinary, NEWID())))

Can someone please explain how it works? I know all the functions used, but I'm unable to link them.

CodePudding user response：

Tne newid() function doesn't actually generate a string, it generates a uniqueidentifier, also known as a "globally unique identifier", or "GUID". This is a pseudo-random value.

The rand(@seed) function generates a value >= 0 and < 1.

Rand doesn't generate a seed, it accepts the seed as an input parameter. The result depends on the seed. If you pass the same seed value as the input parameter, the result of rand() is always the same. If rand() is called without any seed value, SQL server itself will produce a psuedo-random seed.

Now, this probably seems confusing already. Obvious questions are:

If newid() is already pseudo-random, why do we need rand()?

In the code presented in your question, rand() isn't actually being used to generate random values, that job is really being done by newid(). What rand() is doing is mapping the newid() value to a floating point value between zero and one.

OK then, if rand() with no input seed is already psuedo-random, why do we need newid()??

In the specific sample code in your question, where we are only working with a single scalar value, there's actually no need. The same thing could be accomplished with:

select floor(3 * RAND()   1)

However, when you are working with multiple rows of data, rand() doesn't get re-seeded by SQL Server for every row, it only gets seeded once. So if you do something like this:

select rand() from sys.objects

Then every row in the result set will have the same value.

The newid() function is different. SQL will generate a different uniqueidentifier for every single row (it sort of "has to by law" - part of the definition of a GUID is that the same GUID should never be generated twice).

So the newid() function is providing a psuedo-random seed value to rand(), and then rand() is mapping that to some floating point value between 0 and 1 (excluding 1).

What is the convert to varbinary doing?

If an argument is passed to rand(), the argument has to be an integer. A uniqueidentifier cannot be implicitly converted to an integer. But a uniqueidentifier can be converted to a varbinary, and then the varbinary can be implicitly converted to an integer. If we make that conversion explicit, it looks like this:

select convert(int, convert(varbinary, newid()))

In your sample code, the integer conversion is being done implicitly. A uniqueidentifier is 16 bytes long, so it gets converted to a 16 byte varbinary. 12 of those bytes then get silently truncated (thrown away), because an integer is only 4 bytes long. The remaining 4 bytes are implicitly converted to the integer.

Note that this truncation could theoretically weaken the randomness of the result. People often use checksum() to convert to an integer rather than casting through a varbinary, because checksum will make use of all of the bytes in the GUID.

what is the multiplication by 3 doing?

Since the rand() function returns a value between 0 and 1, but you want a value "between" 1 and 3, we have to multiply the result of rand():

• Values >= 0 and < 1/3 will map to values >= 0 and < 1.
• Values >= 1/3 and < 2/3 will map to values >= 1 and < 2.
• Values >= 2/3 and < 1 will map to values >= 2 and < 3.

What is floor() doing?

The value we have right now is some floating point value anywhere between 0 and 3 (excluding 3). But you want only the integer values 1 or 2 or 3. So we have to add 1 and shave off the decimal. This is what the 1 and floor() are doing. You could also get rid of the 1 and replace floor() with ceiling().

CodePudding user response：

I used this query:

select floor(3 * RAND(convert(varbinary, newid()))) 1

• RAND() : returns some seed decimal number like 0.405615055347678
• newid(): returns some string: 29CADAD4-F9F5-4B79-98F0-33DE745954FC
• varbinary : converting data from a string data type to a binary or varbinary data type of unequal length :

eg:

select convert(varbinary, newid())

output:
0x321A7CBE6FBACC41B1EE5BC3C5219B2C

CodePudding user response：

1. We can generate a random number, using the NEWID() function of SQL Server. Random number generated by NEWID() method will be a 32 byte Hexadecimal number, which is unique for your whole system.

2. The unique identifier generated by the NEWID() can be converted to VARBINARY using CONVERT() which in turn can be converted to an integer number USING FLOOR().

3. select rand()-result will be a random decimal

   The FLOOR() function returns the largest integer value that is smaller than or equal to a number.

   select floor(rand()*N) —The generated number is like this: 12.0

   The number range of method: 0 to n-1, such as cast (floor (rand() *100) will generate any integer between 0 and 99.So here our N=3 ... we will be generating an integer between 0 and 3.

   SO, the link is NEWID()>CONVERT()>RAND()>FLOOR>SELECT