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

H开发者_StackOverflowow to write stored procedure in SQL Server 2008 that generates 3 million random no in two columns in integer datatype.

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.

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.