How to get a really random number with MSSQL
- The random function of rand() returns a float between 0 through 1, including 0 and 1.
- The problem is that according to books-on-line: Repetitive calls of RAND() with the same seed value return the same results. Oops!
- According to books-on-line: If seed is not specified, the Microsoft SQL Server 2005 Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same. [emphasis is mine]
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- go
- SELECT counter,emp_short,trans_date,debit,credit
- FROM trans02
- WHERE counter IS not null;
- go
counter emp_short trans_date debit credit ----------- --------- ----------------------- --------------------- --------------------- 10 000190 2007-06-01 00:00:00.000 NULL 125.00 20 000110 2007-06-01 00:00:00.000 NULL 100.00 30 000190 2007-06-02 00:00:00.000 25.00 NULL 40 000030 2007-07-15 00:00:00.000 357.00 NULL 50 000030 2007-07-16 00:00:00.000 NULL 22.00 60 000030 2007-07-18 00:00:00.000 NULL 41.00 70 000030 2007-07-20 00:00:00.000 NULL 29.50 80 000110 2007-07-20 00:00:00.000 42.00 NULL 90 000110 2007-07-22 00:00:00.000 66.00 NULL 100 000110 2007-07-23 00:00:00.000 77.00 NULL 110 000010 2007-07-23 00:00:00.000 11.00 NULL 120 000010 2007-07-24 00:00:00.000 22.56 NULL 130 000010 2007-07-25 00:00:00.000 33.69 NULL (13 row(s) affected)
Random number
- SELECT newid() AS 'New id',
- RAND() AS 'Random not working',
- CHECKSUM(newid()) AS 'Checksum',
- counter,emp_short,trans_date,debit,credit
- FROM trans02
- WHERE counter IS not null
- ORDER BY 3;
New id Random not working Checksum counter emp_short trans_date debit credit ------------------------------------ ---------------------- ----------- ----------- --------- ----------------------- --------------------- --------------------- F137931B-664F-4CB7-B597-03105FD9E96A 0.838972959717442 -1608727751 20 000110 2007-06-01 00:00:00.000 NULL 100.00 3A934F70-5046-4D3D-9F7C-797EAB13B913 0.838972959717442 -1344834565 120 000010 2007-07-24 00:00:00.000 22.56 NULL 41E3CB22-A949-4908-8BC2-B41B5B5BF577 0.838972959717442 -1134949697 100 000110 2007-07-23 00:00:00.000 77.00 NULL 45BCFA1F-BD8F-4592-99D6-B78F306A7504 0.838972959717442 -774042085 60 000030 2007-07-18 00:00:00.000 NULL 41.00 4A976789-B334-4955-A70F-0D85D9C4B2D6 0.838972959717442 -251363326 90 000110 2007-07-22 00:00:00.000 66.00 NULL E5AC2F6A-2C8E-4143-B3B9-A21E8B9D4E7C 0.838972959717442 -247038803 130 000010 2007-07-25 00:00:00.000 33.69 NULL 0F58C42C-7C60-4099-A172-D5C88259AE3D 0.838972959717442 277019267 80 000110 2007-07-20 00:00:00.000 42.00 NULL 5F88145E-A98C-43E0-BF45-100CBFC736AF 0.838972959717442 389707540 30 000190 2007-06-02 00:00:00.000 25.00 NULL F65079CC-7EA0-414E-823B-656E0ED5332B 0.838972959717442 477599931 110 000010 2007-07-23 00:00:00.000 11.00 NULL E6C128E8-B547-4D31-BD13-BE90C77604A6 0.838972959717442 876100622 70 000030 2007-07-20 00:00:00.000 NULL 29.50 7490BBA7-2F6D-4E8E-A399-E1369B38F265 0.838972959717442 1578680847 10 000190 2007-06-01 00:00:00.000 NULL 125.00 65D886A7-5837-4660-AE25-8AABA9DB1975 0.838972959717442 1693370092 40 000030 2007-07-15 00:00:00.000 357.00 NULL A992E547-E740-443C-85C0-679AB2E2A34B 0.838972959717442 2061729487 50 000030 2007-07-16 00:00:00.000 NULL 22.00 (13 row(s) affected)
- NEWID() provides a truly unique 16 bit binary value. It's almost random.
- NEWID() is a 16-byte binary values. It's a unique binary number; no other computer is supposed to generate a duplicate value.
- The NEWID() is generated by the MAC address of the networking card on the server + a unique number from the clock of the computer running the application/query.
- NEWID() is very useful for when you want to make sure that you will get unique values.
- If you need an integer for numbering instead of an alphanumeric string, use the checksum(newid()) and the abs(checksum(newid())).
