Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Generating Test Data: Part 1 - Generating Random Integers and Floats RE: Generating Test Data: Part 1 - Generating Random Integers and Floats

  • March 26, 2012 at 6:36 am

    #1464195

    dwain.c (3/26/2012)

    Outstanding article Jeff! Just what the doctor ordered for something I'm working on at this instant.

    I can't wait for the purists to berate you for using "pseudo" random numbers though. :w00t:

    And let me guess:

    DECLARE @Range INT

    ,@StartValueDATETIME

    ,@EndValueDATETIME

    SELECT @StartValue = '2012-02-15', @EndValue = '2012-12-31'

    SELECT @Range = DATEDIFF(day, @StartValue, @EndValue)

    SELECT TOP 20-- Random dates

    DATEADD(day, ABS(CHECKSUM(NEWID()) % @Range), @StartValue) As SomeRandomTime

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    SELECT @Range = DATEDIFF(second, @StartValue, @EndValue)

    SELECT TOP 20-- Random times (to the second)

    DATEADD(second, ABS(CHECKSUM(NEWID()) % @Range), @StartValue) As SomeRandomDate

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    🙂

    I might be safe for the next 10 minutes or so. Although the "next" random value is certainly predictable, you'd have to know a fair bit about how NEWID() is generated to predict the next value 😀

    You're just about spot on in your code. Just substitute @Range for the 20 in TOP 20 and Bob's your non-hardcoded Uncle. 🙂 In Part 2, I'll explain how to easily include random times as a part of generating random dates. I'll also cover making period bins.

    Thanks for the feedback, Dwain!

    --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)