Generating Random Numbers in T-SQL

The SQL Server T-SQL function RAND() generates random numbers, but it will only generate exactly one such number in a given query (i.e. multiple 'calls' to the function all return the same number).  Another way is to exploit the NEWID() function which returns a UUID (GUID).  At time of writing, the function returns Version 4 UUIDs, which means that most of the bits are random (note the variant or version bits, see the UUID quick summary, below).

So to get a four byte random number, you can use:

select substring(cast(newid() as binary(16)),1,4)+0

Be careful if you try to extract more bits since it is tricky figuring out which bits are which, viz:

  2BDB9E2A-A20D-43F9-9117-2ABCEBD6434E cast to binary(16) is
0x2A9EDB2B 0DA2 F943 9117 2ABCEBD6434E

Note that the leading eight hex octets are switched to low-endian component-wise -- but the other eight octets are not.  The less-than-random hex digits are bolded.