CODEWITHSUNDEEP
sqlt-sqlsql-server-2008stored-procedures
anil
anil

Reputation: 31

How to write stored procedure in SQL Server 2008 that generates 3 million random no

How to write stored procedure in SQL Server 2008 that generates 3 million random no in two columns in integer datatype.

Upvotes: 3

Views: 2356

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300719

SELECT TOP 3000000 
    ABS(CHECKSUM(NewId())) As RndCol1, 
    ABS(CHECKSUM(NewId())) AS RndCol2
FROM 
    sys.objects s1 
    CROSS JOIN sys.objects s2 
    CROSS JOIN sys.objects s3
    CROSS JOIN sys.objects s4

[You may want to check the actual distribution of these numbers using a chi-square test]

UPDATE: The randomness of these may not meet the chi-square distribution test. I would advise Testing in for your circumstances.

Upvotes: 5

Andomar
Andomar

Reputation: 238186

You can use a CTE, for example:

create procedure dbo.GiveMeRandomNumbers
as
    begin
    with qry as (
        select  CAST(CAST(NEWID() AS VARBINARY) AS INT) as col1
        ,       CAST(CAST(NEWID() AS VARBINARY) AS INT) as col2
        ,       0 as i
        union all
        select  CAST(CAST(NEWID() AS VARBINARY) AS INT) as col1
        ,       CAST(CAST(NEWID() AS VARBINARY) AS INT) as col2
        ,       i + 1 
        from    qry
        where   i < 3000000
    )
    select  col1, col2
    from    qry
    option  (maxrecursion 0)
    end

This uses newid because the rnd function will return the same result for each recursive application of the CTE.

Upvotes: 1

Related Questions