How to generate a random number with MSSQL
- Often I need to generate a random number.
- Microsoft SQL Server provides the RAND() function to generate a random number.
- The RAND() function only generate the random number at the evaluation time, not the execution.
- If RAND() is called multiple times within the same stored procedure or select statement or the same transaction, it will return the same number. Not very random.
- Microsoft provide the NEWID() function that get updated at at every call during execution time.
- See: Why is the random function not working?
Applies to:
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
NEWID() does NOT work with SQL Server 2000!
Data used
- USE sql911;
- go
- SELECT emp_short,trans_date,debit,credit FROM trans02;
- go
emp_short trans_date debit credit --------- ----------------------- --------------------- --------------------- 000190 2007-06-01 00:00:00.000 NULL 125.00 000110 2007-06-01 00:00:00.000 NULL 100.00 000190 2007-06-02 00:00:00.000 25.00 NULL 000030 2007-07-15 00:00:00.000 357.00 NULL 000030 2007-07-16 00:00:00.000 NULL 22.00 000030 2007-07-18 00:00:00.000 NULL 41.00 000030 2007-07-20 00:00:00.000 NULL 29.50 000110 2007-07-20 00:00:00.000 42.00 NULL 000110 2007-07-22 00:00:00.000 66.00 NULL 000110 2007-07-23 00:00:00.000 77.00 NULL 000010 2007-07-23 00:00:00.000 11.00 NULL 000010 2007-07-24 00:00:00.000 22.56 NULL 000010 2007-07-25 00:00:00.000 33.69 NULL 000070 2007-09-25 00:00:00.000 NULL 11.11 000070 2007-09-26 00:00:00.000 NULL 11.22 000070 2007-09-27 00:00:00.000 55.00 NULL 000070 2007-10-01 00:00:00.000 52.50 NULL 000160 2007-10-20 00:00:00.000 9.00 NULL 000160 2007-10-20 01:00:00.000 11.50 NULL 000160 2007-10-21 00:00:00.000 NULL 45.00 (20 row(s) affected)
RAND() and random
- SELECT RAND() AS 'Fake random number',
- emp_short,trans_date,debit,credit
- FROM trans02;
- go
Fake random number emp_short trans_date debit credit ---------------------- --------- ----------------------- --------------------- --------------------- 0.851195969536349 000190 2007-06-01 00:00:00.000 NULL 125.00 0.851195969536349 000110 2007-06-01 00:00:00.000 NULL 100.00 0.851195969536349 000190 2007-06-02 00:00:00.000 25.00 NULL 0.851195969536349 000030 2007-07-15 00:00:00.000 357.00 NULL 0.851195969536349 000030 2007-07-16 00:00:00.000 NULL 22.00 0.851195969536349 000030 2007-07-18 00:00:00.000 NULL 41.00 0.851195969536349 000030 2007-07-20 00:00:00.000 NULL 29.50 0.851195969536349 000110 2007-07-20 00:00:00.000 42.00 NULL 0.851195969536349 000110 2007-07-22 00:00:00.000 66.00 NULL 0.851195969536349 000110 2007-07-23 00:00:00.000 77.00 NULL 0.851195969536349 000010 2007-07-23 00:00:00.000 11.00 NULL 0.851195969536349 000010 2007-07-24 00:00:00.000 22.56 NULL 0.851195969536349 000010 2007-07-25 00:00:00.000 33.69 NULL 0.851195969536349 000070 2007-09-25 00:00:00.000 NULL 11.11 0.851195969536349 000070 2007-09-26 00:00:00.000 NULL 11.22 0.851195969536349 000070 2007-09-27 00:00:00.000 55.00 NULL 0.851195969536349 000070 2007-10-01 00:00:00.000 52.50 NULL 0.851195969536349 000160 2007-10-20 00:00:00.000 9.00 NULL 0.851195969536349 000160 2007-10-20 01:00:00.000 11.50 NULL 0.851195969536349 000160 2007-10-21 00:00:00.000 NULL 45.00 (20 row(s) affected)
- As you can see from this example, the random number is the same for all the rows retrieve.
- RAND() did generate a random number: 0.851195969536349, but RAND() is only calculated once at evaluation time.
Real random number
- SELECT ABS(CAST(CAST(newid() AS VARBINARY) AS INT)) AS 'Integer random number',
- emp_short,trans_date,debit,credit
- FROM trans02;
- go
Integer random number emp_short trans_date debit credit --------------------- --------- ----------------------- --------------------- --------------------- 1452746779 000190 2007-06-01 00:00:00.000 NULL 125.00 1511506064 000110 2007-06-01 00:00:00.000 NULL 100.00 1189697883 000190 2007-06-02 00:00:00.000 25.00 NULL 1767010407 000030 2007-07-15 00:00:00.000 357.00 NULL 1522053383 000030 2007-07-16 00:00:00.000 NULL 22.00 335397851 000030 2007-07-18 00:00:00.000 NULL 41.00 913022124 000030 2007-07-20 00:00:00.000 NULL 29.50 1888662992 000110 2007-07-20 00:00:00.000 42.00 NULL 2074970679 000110 2007-07-22 00:00:00.000 66.00 NULL 219143447 000110 2007-07-23 00:00:00.000 77.00 NULL 1618934769 000010 2007-07-23 00:00:00.000 11.00 NULL 1355023251 000010 2007-07-24 00:00:00.000 22.56 NULL 469286489 000010 2007-07-25 00:00:00.000 33.69 NULL 522573779 000070 2007-09-25 00:00:00.000 NULL 11.11 1589686790 000070 2007-09-26 00:00:00.000 NULL 11.22 229719820 000070 2007-09-27 00:00:00.000 55.00 NULL 1272022157 000070 2007-10-01 00:00:00.000 52.50 NULL 1266410131 000160 2007-10-20 00:00:00.000 9.00 NULL 1870771437 000160 2007-10-20 01:00:00.000 11.50 NULL 945380068 000160 2007-10-21 00:00:00.000 NULL 45.00 (20 row(s) affected)
- NEWID() Creates a unique value of type uniqueidentifier.
- It's an hexadecimal value derived from the MAC address of the network card plus a unique number from the CPU clock.
- Line 1: abs(cast(cast(newid() as varbinary) as int)) converts the hexadecimal number to an integer.
NEWID conversion to integer
- SELECT newid() AS 'Newid',
- CAST(newid() AS VARBINARY) AS 'Binary newid',
- CAST(CAST(newid() AS VARBINARY) AS INT) AS 'Integer newid',
- ABS(CAST(CAST(newid() AS VARBINARY) AS INT)) AS 'Integer random number',
- emp_short,trans_date,debit,credit
- FROM trans02;
- go
Newid Binary newid Integer newid Integer random number emp_short trans_date debit credit ------------------------------------ -------------------------------------------------------------- ------------- --------------------- --------- ----------------------- --------------------- --------------------- 8D860AC4-BA39-4D12-91CF-F6F20AF9D59E 0xA9310D064DDB664AB67E58D4FA6140AE 1042029786 734587047 000190 2007-06-01 00:00:00.000 NULL 125.00 9C4BC55D-3D34-4614-9176-F79A51C6A7F3 0x6DE131C5C73E7A4E95F7F89544553F42 1198993494 1902315030 000110 2007-06-01 00:00:00.000 NULL 100.00 52C4156C-5009-470D-839C-E88BE480F6E0 0xCAF7CF433847204F8F8B3588812BE88A 1032539244 477287890 000190 2007-06-02 00:00:00.000 25.00 NULL C16422A8-0A17-4018-8067-D8BBBB6826AF 0xA17900DCB18C7E45AE07F9CDA9DDCBE8 -1815203699 168370402 000030 2007-07-15 00:00:00.000 357.00 NULL 1978C40D-6EED-4FBB-8BFB-FCA3FCF6C4D5 0x230041D9D1A94C49B9155F11C7284CC1 -528147515 1390539847 000030 2007-07-16 00:00:00.000 NULL 22.00 34B5A0E5-C6B6-46A8-9F88-7607A6C8FE46 0x5D049C513A93D949A9882F4D1B94C343 -500859888 1634272795 000030 2007-07-18 00:00:00.000 NULL 41.00 5F6F4C86-83BA-4C40-9790-1B31819029C4 0x474433717DC422478E32B85EEADF84FC 261480181 519147089 000030 2007-07-20 00:00:00.000 NULL 29.50 8FEE40A0-5236-431C-BE82-5089C1DAC7C1 0x96D21B3504331B449F740CEF31087835 -387703423 80932913 000110 2007-07-20 00:00:00.000 42.00 NULL 8ECE248C-6CD7-42DD-A497-2B36BDBBA617 0x2443EE6698126D4CBBE7B4FA050D9F8D -256838228 551421988 000110 2007-07-22 00:00:00.000 66.00 NULL BE9B4A6F-FFA9-48F3-BC2B-531388AB4C2B 0xC4277A3D4B41364B918EC2039E32BA8C 1004216926 1922867090 000110 2007-07-23 00:00:00.000 77.00 NULL F5AE9A9F-96B2-414E-A449-F64A07CAF32F 0xA4B24C6986581A44AD0173B21C01E7DE 993491760 1702907948 000010 2007-07-23 00:00:00.000 11.00 NULL A4F2C7DF-3CC7-4F8A-A610-31CC451E813C 0x18482C82D9A9DE4384A42C48BEAFD105 2042188938 48829800 000010 2007-07-24 00:00:00.000 22.56 NULL 76D68B35-17DE-4524-A622-0EFB356A07B6 0x7E3698704127F047BA659C5214E5434D -455822190 414625411 000010 2007-07-25 00:00:00.000 33.69 NULL 4A8857B5-EFD8-482B-AA9A-F2229A42040A 0xC06B633E384ADD4BA563569EA748A953 99199592 2109326246 000070 2007-09-25 00:00:00.000 NULL 11.11 11C0038D-A122-403D-820D-FA7F3D976480 0x051C81641B5B4C42903506D4F9869843 -1052003717 832391423 000070 2007-09-26 00:00:00.000 NULL 11.22 1B890742-7CCD-4F63-8F39-2482046C420E 0xB788BB0908998145A64E5413FE56FD37 -30965911 2085101769 000070 2007-09-27 00:00:00.000 55.00 NULL 058CDAEE-C0C5-4C6A-BB14-D61D2E33B44A 0x1A5AD767A87D7147A68B7B8BE59842D6 -578184588 1264117530 000070 2007-10-01 00:00:00.000 52.50 NULL 0491BD10-9086-4D08-B163-BFE99553A37A 0x1CF0D32DF73A8E4289A0CF68A90172CB -1195859560 1411832937 000160 2007-10-20 00:00:00.000 9.00 NULL 785DE5DA-9694-4E74-8CF3-4F5E2DBCAB63 0x6E11B2B80A6CCD49BCF5A26571CA5336 321285309 1998516011 000160 2007-10-20 01:00:00.000 11.50 NULL 0F9230C1-E05D-48A5-B1C2-A55B3674C3A3 0x59A1B5D15A39DC40B42D56C365391D74 1890845217 503372419 000160 2007-10-21 00:00:00.000 NULL 45.00 (20 row(s) affected)
- Note that each time we call the NEWID function is reevaluated brand new.
- Note that the values of line 3: and the values of line 4: do not match! That's because each call to NEWID() generate a brand new random number.