Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Random number generator Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 4:23 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:52 PM
Points: 8, Visits: 88
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.

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

;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
Post #1446745
Posted Thursday, April 25, 2013 5:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:51 PM
Points: 1,787, Visits: 5,694
Gosh - didn't see the date ! sorry!

MM


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1446753
    Posted Thursday, April 25, 2013 11:35 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 8:35 AM
    Points: 36,977, Visits: 31,494
    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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1446801
    Posted Friday, April 26, 2013 12:54 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 8:35 AM
    Points: 36,977, Visits: 31,494
    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.

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

    ;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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1446817
    Posted Friday, April 26, 2013 3:09 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: 2 days ago @ 3:51 PM
    Points: 1,787, Visits: 5,694
    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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1446863
    Posted Friday, April 26, 2013 7:41 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 8:35 AM
    Points: 36,977, Visits: 31,494
    Heh... got it and understood. It was a really open ended original question.

    --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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1446998
    Posted Friday, April 26, 2013 11:16 AM


    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, May 20, 2013 9:52 PM
    Points: 8, Visits: 88
    Hi MM,

    I was assigning random numbers to each row because I was generating test data. In my case, I was simulating data returned from Market Research Surveys. The questions use a scale from 1 to 5 or 1 to 10. I wanted to randomly alot ratings to my test data.


    www.sqlwithcindy.com
    Post #1447105
    Posted Friday, April 26, 2013 11:18 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: 2 days ago @ 3:51 PM
    Points: 1,787, Visits: 5,694
    Cindy Conway-312336 (4/26/2013)
    Hi MM,

    I was assigning random numbers to each row because I was generating test data. In my case, I was simulating data returned from Market Research Surveys. The questions use a scale from 1 to 5 or 1 to 10. I wanted to randomly alot ratings to my test data.


    See, that's why I'm not a mind reader, I would never have guessed that!

    Good to know anyway, thanks


    MM


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1447107
    Posted Friday, April 26, 2013 11:27 AM


    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, May 20, 2013 9:52 PM
    Points: 8, Visits: 88
    Hi Jeff,

    Nice code! Clever how you did that. I knew that it could be done in t-sql (although I don't think I would have come up with a function as clever as yours). I like C# for things like random numbers and complex string manipulation. The functionality built in is more elegant IMO. The simple update statement you listed is great too, but it looses the ability to specify a lower and upper range to the random numbers. Like you said, performance is pretty equal. The CLR outperforms on very big data sets (1million+), but only by a small amount .

    Anyone who reads this post gets to choose the option best suited to their particular situation.


    www.sqlwithcindy.com
    Post #1447109
    Posted Monday, April 29, 2013 6:24 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 8:35 AM
    Points: 36,977, Visits: 31,494
    Cindy Conway-312336 (4/26/2013)
    The simple update statement you listed is great too, but it looses the ability to specify a lower and upper range to the random numbers.


    Thanks for the feedback, Cindy. However, not quite true on the upper and lower range of random numbers (integers, in this case). It uses the same basic formula that a lot of random number generators use to specify the domain to be returned. If you look carefully at the function, it takes min and max values just like your CLR function does.


    --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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1447475
    « Prev Topic | Next Topic »

    Add to briefcase ««123»»

    Permissions Expand / Collapse