2

I have a table, Benefit, where the policy number is to be generated randomly and should be unique of course. Below are my statements and query.

INSERT INTO Benefit([Company Name], [Policy #]) 
VALUES ('Assumption Life', RAND(100) * 100)

INSERT INTO Benefit([Company Name], [Policy #]) 
VALUES ('Aviva Canada', RAND(1) * 12)

INSERT INTO Benefit([Company Name], [Policy #]) 
VALUES ('Blue Cross' RAND(100) * 100)

INSERT INTO Benefit([Company Name], [Policy #]) 
VALUES ('Health & Dental Insurance' RAND(100) * 100)

I get the response:

Started executing query at Line 123
(1 row affected)
(1 row affected)

Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UQ__Benefit__2E118E21CDDEB4E7'. Cannot insert duplicate key in object 'dbo.Benefit'. The duplicate key value is (71).

Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'UQ__Benefit__2E118E21CDDEB4E7'. Cannot insert duplicate key in object 'dbo.Benefit'. The duplicate key value is (71).

The first two are successfully executed as the RAND() functions are a bit different, but from 3rd it throws a duplication exception, since it is same as 1st record.

I tried RAND(), and it always assigns 0. And from second row, throws same error.

Why it doesn't generate random number? Am I not clear with how to use RAND() function?

Community
  • 1
  • 1
Veronica
  • 181
  • 1
  • 11
  • 5
    Please consult the [docs](https://learn.microsoft.com/en-us/sql/t-sql/functions/rand-transact-sql?view=sql-server-ver15), *For a specified seed value, the result returned is always the same.* i.e. you can only use it once else you reset the sequence. – Dale K Feb 14 '20 at 03:59
  • please show your table schema definition – Squirrel Feb 14 '20 at 04:00
  • 3
    also from the doc `Repetitive calls of RAND() with the same seed value return the same results.` – Squirrel Feb 14 '20 at 04:00
  • 2
    As @DaleK stated, the returned `RAND` value will always be the same. I would suggest maybe using a `SEQUENCE` to get your value - this can be better controled yo give unique values, or just add a `DEFAULT` constraint to the column as an `IDENTITY` – Martin Cairney Feb 14 '20 at 04:03
  • @MartinCairney I have PK to be generated as IDENTITY so cannot use it again in the same table. But can you please elaborate a bit or provide some reference on SEQENCE? – Veronica Feb 14 '20 at 04:08
  • 2
    refer to the doc on sequence – Squirrel Feb 14 '20 at 04:10
  • 2
    @Kopal refer to the [docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15) for `SEQUENCE` – Martin Cairney Feb 14 '20 at 04:35
  • @DaleK yes I checked many questions on this RAND() function but none helped. Although the solution I accepted did. – Veronica Feb 14 '20 at 05:20
  • @Kopal the suggested duplicate exactly answers your question *Why it doesn't generate a random number*. But maybe your question was actually how to generate a unique random sequence? – Dale K Feb 14 '20 at 05:23

1 Answers1

1

This explains the usage of RAND

The statement of interest is "The RAND function will return a repeatable sequence of random numbers each time a particular seed value is used."

To create a random decimal number between two values (range), you can use the following formula:

SELECT RAND()*(b-a)+a;
Dale K
  • 25,246
  • 15
  • 42
  • 71
Savin
  • 131
  • 5