Random number generator

  • Ok.Guys I need your help. I have a table of about 60000 records. I want to add a column say random number and store it in the table

    1. I want to generate a random number for each row and store it in the table

    2. I do not want to write a seperate function and call it. I want to do it in one step .is it possible ?

  • What range and what datatype?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Oh, and "how" random? Linear congruential differentiator random? Cryptographically strong random? More? Or less? Or in-between?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • sudrav (10/15/2008)


    Ok.Guys I need your help. I have a table of about 60000 records. I want to add a column say random number and store it in the table

    1. I want to generate a random number for each row and store it in the table

    2. I do not want to write a seperate function and call it. I want to do it in one step .is it possible ?

    Pick one...

    DROP TABLE JBMTest

    GO

    --===== Create and populate a 1,000,000 row test table.

    -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

    -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

    -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

    -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

    -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

    -- for all rows.

    -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

    -- Jeff Moden

    SELECT TOP 1000000

    RowNum = IDENTITY(INT,1,1),

    SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

    SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65),

    SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

    SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

    SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

    SomeHex12 = RIGHT(NEWID(),12)

    INTO dbo.JBMTest

    FROM Master.dbo.SysColumns t1

    CROSS JOIN Master.dbo.SysColumns t2

    --===== A table is not properly formed unless a Primary Key has been assigned

    ALTER TABLE dbo.JBMTest

    ADD PRIMARY KEY CLUSTERED (RowNum)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • So that's in-between LGD and Cryptographic? And I don't see any varbinary(MAX) or sql_variant data-types. 😀

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • rbarryyoung (10/15/2008)


    So that's in-between LGD and Cryptographic? And I don't see any varbinary(MAX) or sql_variant data-types. 😀

    Heh... no... It's somewhere in between a pork chop and a chicken leg. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff - enquiring minds wanna know - how are you getting your code windows to scroll side to side?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (10/15/2008)


    Jeff - enquiring minds wanna know - how are you getting your code windows to scroll side to side?

    I think Steve's in-house Ninja tweeked the forum code for it very recently...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • See this small example and check that helps.

    http://praveenbattula.blogspot.com/2009/05/how-to-generate-random-numbers-in-t-sql.html

  • battula.praveen (3/29/2010)


    See this small example and check that helps.

    http://praveenbattula.blogspot.com/2009/05/how-to-generate-random-numbers-in-t-sql.html

    The only problem with that is that it uses RAND() alone and is good for only one row at a time.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I've created a CLR TVF to handle this. I find C# easier to use than t-SQL for random numbers within a range. My TVF returns a uniqueKey along with the random numbers, so I just join the rowNumber() with the uniqueKey. The update statement ends up looking like this. Here is how to create the TVF that generated the random numbers.

    ;WITH clientCTE AS

    (

    SELECT

    ROW_NUMBER() OVER(ORDER BY int_ID) AS 'RowNumber',

    randomNumber

    FROM

    client

    )

    UPDATE clientCTE

    SET randomNumber = rn.RandomNumber

    FROM dbo.tvfRandomNumberList(1,5,1000) AS rn

    WHERE rn.UniqueKey = clientCTE.RowNumber

    www.sqlwithcindy.com

  • Gosh - didn't see the date ! sorry!

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (4/25/2013)


    Gosh - didn't see the date ! sorry!

    Don't let dates of posts scare you away. What did you have to say?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Cindy Conway-312336 (4/25/2013)


    I've created a CLR TVF to handle this. I find C# easier to use than t-SQL for random numbers within a range. My TVF returns a uniqueKey along with the random numbers, so I just join the rowNumber() with the uniqueKey. The update statement ends up looking like this. Here is how to create the TVF that generated the random numbers.

    ;WITH clientCTE AS

    (

    SELECT

    ROW_NUMBER() OVER(ORDER BY int_ID) AS 'RowNumber',

    randomNumber

    FROM

    client

    )

    UPDATE clientCTE

    SET randomNumber = rn.RandomNumber

    FROM dbo.tvfRandomNumberList(1,5,1000) AS rn

    WHERE rn.UniqueKey = clientCTE.RowNumber

    Hi Cindy,

    Nice code.

    Random numbers just aren't that difficult to handle in T-SQL. With just a little of the "Black Arts", it's easy to create a direct replacement for your fine CLR using only T-SQL. Here's the code to do such a thing. I've kept on with your naming convention and shamelessly borrowed the header comments from your CLR. Its performance is probably second only to a CLR such as yours.

    CREATE VIEW dbo.vUnconstrainedRandomInt WITH SCHEMABINDING AS

    /***************************************************************************************************

    Returns an unconstrained random integer suitable for use virtually anywhere including in a function

    ***************************************************************************************************/

    SELECT UnconstrainedRandomInt = ABS(CHECKSUM(NEWID()));

    GO

    CREATE FUNCTION dbo.tvfRandomNumberList

    /***************************************************************************************************

    Function returns a list of randomly generated numbers with a UniqueKey.

    @pMinValue = the mimiumn value of the random numbers generated.

    @pMaxValue = the maximum value of the random numbers generated.

    @pSize = the number of random numbers to be returned.

    This code is a direct replacement for the CLR function which may be found at the following URL.

    http://www.sqlwithcindy.com/2013/04/elegant-random-number-list-in-sql-server.html

    ***************************************************************************************************/

    --===== Declare the I/O for this function

    (

    @pMinValue INT

    , @pMaxValue INT

    , @pSize INT

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    --===== Create and return the random number list

    RETURN WITH

    E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 --10E1 or up to 10 rows

    )

    , E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or up to 1,000 rows

    , E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or up to 1,000,000,000 rows

    SELECT TOP (@pSize)

    UniqueKey = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    , RandomNumber = ri.UnconstrainedRandomInt %(@pMaxValue-@pMinValue+1) +@pMinValue

    FROM E9, dbo.vUnconstrainedRandomInt ri

    ;

    The truth be told, though, the generation of random numbers don't need such sophistication as an iTVF. For example, you wrote the following code to assign random numbers to the "Client" table.

    ;WITH clientCTE AS

    (

    SELECT

    ROW_NUMBER() OVER(ORDER BY int_ID) AS 'RowNumber',

    randomNumber

    FROM

    client

    )

    UPDATE clientCTE

    SET randomNumber = rn.RandomNumber

    FROM dbo.tvfRandomNumberList(1,5,1000) AS rn

    WHERE rn.UniqueKey = clientCTE.RowNumber

    Again, using some of the "Black Arts" available in T-SQL, the code can be greatly simplified and the expense of a join can be avoided thusly...

    UPDATE dbo.Client

    SET RandomNumber = ABS(CHECKSUM(NEWID())) %5 +1

    ;

    For more information on the generation of random Integers, random Floats, and random Dates with or without Times, please see the following articles:

    http://www.sqlservercentral.com/articles/Data+Generation/87901/

    http://www.sqlservercentral.com/articles/Test+Data/88964/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (4/25/2013)


    mister.magoo (4/25/2013)


    Gosh - didn't see the date ! sorry!

    Don't let dates of posts scare you away. What did you have to say?

    Oh nothing important, just questioning the intent - whenever I see people asking for specific things in a non-specific way it makes me wonder what they really want. In this case, did the OP actually want random numbers assigned to each row - for what purpose? I can't think of any reasonable reason for random numbers being assigned to the rows unless it is for consistent/repeatable "random" ordering or consistent/repeatable "random" selection, and if it is one of those, do they want "random" numbers (with the possibility of duplicates - or even the low possibility of them all being assigned the same number) or unique numbers but randomly assigned ?

    However , I spotted that this was another old post being added to randomly and thought there was not much likelihood of the OP still looking for an answer 5 years one, so backed off 😛

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

    Viewing 15 posts - 1 through 15 (of 20 total)

    You must be logged in to reply to this topic. Login to reply