Why is the random function not working?
- 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.
- 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.
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 not working
- SELECT RAND() AS 'Random #',counter,emp_short,trans_date,debit,credit
- FROM trans02
- WHERE counter IS not null;
- go
Random # counter emp_short trans_date debit credit ---------------------- ----------- --------- ----------------------- --------------------- --------------------- 0.78687395623786 10 000190 2007-06-01 00:00:00.000 NULL 125.00 0.78687395623786 20 000110 2007-06-01 00:00:00.000 NULL 100.00 0.78687395623786 30 000190 2007-06-02 00:00:00.000 25.00 NULL 0.78687395623786 40 000030 2007-07-15 00:00:00.000 357.00 NULL 0.78687395623786 50 000030 2007-07-16 00:00:00.000 NULL 22.00 0.78687395623786 60 000030 2007-07-18 00:00:00.000 NULL 41.00 0.78687395623786 70 000030 2007-07-20 00:00:00.000 NULL 29.50 0.78687395623786 80 000110 2007-07-20 00:00:00.000 42.00 NULL 0.78687395623786 90 000110 2007-07-22 00:00:00.000 66.00 NULL 0.78687395623786 100 000110 2007-07-23 00:00:00.000 77.00 NULL 0.78687395623786 110 000010 2007-07-23 00:00:00.000 11.00 NULL 0.78687395623786 120 000010 2007-07-24 00:00:00.000 22.56 NULL 0.78687395623786 130 000010 2007-07-25 00:00:00.000 33.69 NULL (13 row(s) affected)
- Take a look at 'Random #', they are all the same for every row.
- Rand() is not working. It's working as books-on-line describes it, but not as expected.
- The number generated was random, but then, it's being reused until the next call of rand() by either a new select or a new stored procedure or a loop that calls again the rand().
Random working
- MSSQL has a new function NEWID() that works.
- NEWID() is 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.
- SELECT newid() AS 'Really random',counter,emp_short,trans_date,debit,credit
- FROM trans02
- WHERE counter IS not null;
Really random counter emp_short trans_date debit credit ------------------------------------ ----------- --------- ----------------------- --------------------- --------------------- B3A6C651-1574-4402-85A7-150991B042AD 10 000190 2007-06-01 00:00:00.000 NULL 125.00 AD8143A6-BF7E-43D4-ADAC-915BC02C0F91 20 000110 2007-06-01 00:00:00.000 NULL 100.00 16EE89BF-8642-4853-89F6-9B7150EA8FEC 30 000190 2007-06-02 00:00:00.000 25.00 NULL EEC04E5D-F58D-4F65-9CCC-D0AFC2381867 40 000030 2007-07-15 00:00:00.000 357.00 NULL F5569A78-94F7-4F7B-80BB-088308B1B6F0 50 000030 2007-07-16 00:00:00.000 NULL 22.00 A5875DF8-36C1-4EE7-9328-4531018A672F 60 000030 2007-07-18 00:00:00.000 NULL 41.00 09AD3799-8B3D-4817-9A3D-F37828157A37 70 000030 2007-07-20 00:00:00.000 NULL 29.50 896A3C14-70FB-4A0F-B5CA-224FBF296B6A 80 000110 2007-07-20 00:00:00.000 42.00 NULL 43C9F182-8D66-4DB8-BAC0-C35796020B23 90 000110 2007-07-22 00:00:00.000 66.00 NULL BFB3F042-3A2C-4CEA-B267-FAA68261B347 100 000110 2007-07-23 00:00:00.000 77.00 NULL 56981170-44A8-4427-8FE0-F86BCF7E8782 110 000010 2007-07-23 00:00:00.000 11.00 NULL 043D8DFA-9E3E-45CF-9A0A-40E34EF0AC44 120 000010 2007-07-24 00:00:00.000 22.56 NULL CE5B29F5-E261-4810-B9B6-AF1C4DE0C69C 130 000010 2007-07-25 00:00:00.000 33.69 NULL (13 row(s) affected)