0

I want to to generate 100000 no repeat random numbers in SQL SERVER2008.

These numbers must be between 1 and 100000. like below:

RandNumber

   100
   9
   1000
   34
   67
   546
   64000
user3757263
  • 45
  • 1
  • 5
  • 1
    http://stackoverflow.com/questions/19875588/generate-random-numbers-using-sql – Meysam Tolouee Jun 22 '14 at 06:34
  • 1
    If you want 100,000 numbers between 1 and 100,000 that don't have duplicates there is only one set of numbers that fits anyway! Are you asking how to order these randomly? – Martin Smith Jun 22 '14 at 06:37
  • 1
    How can it be a duplicate of a question that doesn't have a "no repeat" constraint? Ordering the numbers 1..100000 randomly is different than picking 1000000 random numbers, which may be the same. Please pay attention to the question itself, not just the title. – user2864740 Jun 22 '14 at 06:50
  • @ Martin Smith I want to to generate 100000 no repeat random numbers insert into a empty table – user3757263 Jun 22 '14 at 06:56
  • Well if the numbers are integers then the only way of getting 100,000 between 1 and 100,000 is to use all the integers in that range. So there is no randomness there at all. – Martin Smith Jun 22 '14 at 07:01
  • @MartinSmith There are 52 cards in a standard [French deck](http://en.wikipedia.org/wiki/Standard_52-card_deck); yet the cards are - cheating and poor shuffling aside - in a random order after being shuffled. – user2864740 Jun 22 '14 at 07:16
  • @user2864740 My first comment talks about ordering. The OP says they want to insert these numbers into a table. Rows in a table have no order. – Martin Smith Jun 22 '14 at 07:18

4 Answers4

4

Since there is no meaning to the order of records in an sql table, all you have to do is create a table with consecutive integer values from 1 to 100000. Say, something like that:

--Create the table and populate it
SELECT TOP 100000 IDENTITY(int,1,1) AS IntValue 
INTO NumbersTable 
FROM sys.objects s1     
CROSS JOIN sys.objects s2 

-- Set the IntValue column as the primary key and clustered index
ALTER TABLE NumbersTable ADD CONSTRAINT PK_NumbersTable PRIMARY KEY CLUSTERED (IntValue)

From that table you can then select the numbers in a random order using a query like this:

-- Select values in a random order
SELECT IntValue
FROM NumbersTable 
ORDER BY NEWID()

NEWID() function generates a uniqueIdentifier (GUID). since guids are created in a random order, you can use it to sort the query output randomly.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • `INSERT INTO dbo.Test(ID) SELECT IntValue FROM NumbersTable ORDER BY NEWID()` not random order – user3757263 Jun 22 '14 at 07:26
  • @user3757263 Because from a relational perspective rows in a table are unordered. Therefore SQL Server 2012+ just optimises the `ORDER BY` out here [and ignores it](http://stackoverflow.com/questions/11222043/table-valued-function-order-by-is-ignored-in-output/11231935#11231935). – Martin Smith Jun 22 '14 at 07:38
  • I started my answer with **Since there is no meaning to the order of records in an sql table**. as Martin Smith said, you can't sort records when inserting them to a table. – Zohar Peled Jun 22 '14 at 08:00
1

You can use a recursive CTE to generate the numbers.

CREATE TABLE [Numbers]
  (
     [Number] INT,
     CONSTRAINT [PK_Numbers] PRIMARY KEY ([Number])
  );

WITH [cteNumbers]
     AS (SELECT 1 AS [Number]
         UNION ALL
         SELECT [cteNumbers].[Number] + 1
         FROM   [cteNumbers]
         WHERE  [cteNumbers].[Number] < 100000)
INSERT INTO [Numbers]
            ([Number])
SELECT [cteNumbers].[Number]
FROM   [cteNumbers]
OPTION (MAXRECURSION 0);

Note that with a recursive CTE it's not necessary to store the numbers in a table to use them, you can simply use the recursive CTE definition where it's needed.

Just remember to include OPTION (MAXRECURSION 0).

If you want to use the numbers in a random order, order them randomly.

Community
  • 1
  • 1
ta.speot.is
  • 26,914
  • 8
  • 68
  • 96
1

I think instead of using WHILE LOOP, the following method will be better:

DECLARE @TempTable TABLE( Numbers INT)

;WITH CTE AS
(
    SELECT 0 Units UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 
)
INSERT INTO @TempTable( Numbers)
SELECT Unit.Units + Hundred.Units * 10 + Thousand.Units * 100 
       + Lakh.Units * 1000 + TenLakhs.Units * 10000 + 1 Numbers
FROM CTE Unit,
CTE Hundred,
CTE Thousand,
CTE Lakh,
CTE TenLakhs


SELECT *
FROM @TempTable

Just a simple maths used in the above query.

Nithin Gangadharan
  • 527
  • 4
  • 9
0
CREATE TABLE Numbers (id bigint IDENTITY(1,1) NOT NULL)
INSERT Numbers DEFAULT VALUES
GO 100000
SELECT id FROM Numbers ORDER BY NewID()
GO
Jared Nielsen
  • 13
  • 5