CODEWITHSUNDEEP
sql-serversql-server-2008
user3757263
user3757263

Reputation: 45

How to generate 100000 no repeat random numbers in SQL SERVER2008

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

Upvotes: 0

Views: 7333

Answers (4)

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 4

Jared Nielsen
Jared Nielsen

Reputation: 13

CREATE TABLE Numbers (id bigint IDENTITY(1,1) NOT NULL)
INSERT Numbers DEFAULT VALUES
GO 100000
SELECT id FROM Numbers ORDER BY NewID()
GO

Upvotes: 0

Nithin Gangadharan
Nithin Gangadharan

Reputation: 527

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.

Upvotes: 1

ta.speot.is
ta.speot.is

Reputation: 27214

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.

Upvotes: 1

Related Questions