1

How do I populate a int column, currently empty, with random numbers with no duplicates?

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
  • 1
    How many numbers? What range should the numbers be in? Which database engine? – Barry Brown May 10 '09 at 02:02
  • 8
    Random numbers without duplicates aren't random numbers. – Restore the Data Dumps May 10 '09 at 02:04
  • 1
    "Random numbers without duplicates aren't random numbers." That's not correct. You can perform random sampling without replacement. For example, this is how political polling is done, when done correctly. – Alex Reynolds May 10 '09 at 02:16
  • @Alex Reynolds: Care to put your definition of random up against mine in a cryptographic showdown with your identity on the line? – Restore the Data Dumps May 10 '09 at 02:31
  • 1
    Sure. Search Google on "hypergeometric distribution". You can certainly create a random number generator, where you are sampling without replacement. – Alex Reynolds May 10 '09 at 02:36
  • @Alex Reynolds: Googled...good read but I still don't get it. It's probably my failing, not yours. Can you explain how sampling without replacement from a population of 10 will produce a cryptographically secure 10th message? If you send messages using 1,2,3,4,5,6,7,8 and 9 and Eve is able to deduce that, how is the 10th message supposed to be secure? If I uniformly sample from 1 to 10 even if Eve knows my first 9 keys she can't deduce the 10th. – Restore the Data Dumps May 10 '09 at 03:14
  • What does that have to do with the decon's question? He wants random numbers with no duplicates. Did you even read the question? – Alex Reynolds May 10 '09 at 17:33
  • Uh, you're welcome? Did you read the question? – Alex Reynolds May 11 '09 at 00:01
  • Random does not imply cryptographic security. If you are sampling a number at random in the range [1, 10], it's not cryptographically secure regardless of how the number is chosen, because it will take at most 10 tries to guess it correctly. Similarly, choosing 9 numbers with no duplicates is every bit as secure as choosing 1 number with no duplicates. It's essentially the same operation. But again, cryptographic security is not the issue. Randomness is. If the user wants a set of non-duplicating numbers, the shuffling of those numbers can still be random. – jcdyer Nov 27 '12 at 18:41

2 Answers2

9

If this is an existing table to which you added a new INT column, you can do something like this:

UPDATE MyTable
SET MyIntColumn = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000);

This will populate the empty column with random numbers between 1 and 10000.

Jose Basilio
  • 50,714
  • 13
  • 121
  • 117
  • I think the range would be between 0 and 9999, but this does work nicely. I wonder what it's doing though... without the `CHECKSUM(NEWID())` part you just get the same number forever. I guess you need to seed with different values to get different values? – Dave Cousineau Jan 09 '15 at 20:08
1

I suppose you could make the column a primary key to prevent duplicates, though that's kind of a hack. You can remove the key later.

---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT;
DECLARE @Index integer

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SET @Index = 0 --- A while loop counter

--- Loop from 0 to 10
WHILE @Index < 10
BEGIN
  SELECT 'loop counter = ', @index
  SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
  --Insert @Random here.
  SET @index = @index + 1
END
Daniel
  • 10,864
  • 22
  • 84
  • 115
  • Nice one, although since the OP asked for 'no duplicates' you should probably use a IF NOT EXISTS somewhere... – edosoft May 10 '09 at 18:04