Generating Completely Random Sample Output from a Dataset

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4805

    Comments posted to this topic are about the item Generating Completely Random Sample Output from a Dataset

  • nir-616899

    SSC Rookie

    Points: 43

    Hi,
    Thank you for the quick tip.

    I believe a follow up is in need.

    In case the manager asks for a new batch of random customers after a while,
    The "on the fly" GUID(NewID), although it is generated uniquely per run,
    might produce some of the customers that were on the previous run.

    Therefore Iā€™d suggest to add a BIT type column (0,1) to monitor which customers were called,

    so you could mark them up and exclude them from your next runs.

    SELECT TOP 5 PERCENT *
    FROM   Data.SalesByCountry
    WHERE Called = 0 -- 0 = not called, 1 = called
    ORDER BY NEWID()

    Make sure you update the [Called] column to '1'
    for  the customers you called.

    A select with output/update query will do it at one go.

  • Chuck Bevitt

    Right there with Babe

    Points: 754

    Interesting, because I had thought that any function that took no parameters or a hard coded value parameter would only be evaluated once and would return the same value for every record in the returning record set. Examples of this are Rand() and GetDate(). So it appears that NewID() is an exception. I just had to test this:

    CREATE TABLE dbo.TestFunctions(
     PrimaryKey int IDENTITY(1,1) NOT NULL,
     UniqueID uniqueidentifier NOT NULL,
     Random float NOT NULL,
     CONSTRAINT PK_TestFunctions PRIMARY KEY CLUSTERED (PrimaryKey ASC))

    And then:

    Insert into dbo.TestFunctions (UniqueID, Random)
    Select top 10
     NewID(),
     Rand()
    From Messages

    Where 'Messages' is any table with at least 10 records. Sure enough, UniqueID gets different values while Random gets the same value in every record.

    The fact that Rand() returns the same value for every record has been a source of frustration and innumerable forum threads over the years. As the article illustrates, using NewID() instead of Rand() would solve the problem posted in these forum threads.

  • Jeff Moden

    SSC Guru

    Points: 994661

    Chuck Bevitt - Thursday, July 12, 2018 12:03 PM

    Interesting, because I had thought that any function that took no parameters or a hard coded value parameter would only be evaluated once and would return the same value for every record in the returning record set. Examples of this are Rand() and GetDate(). So it appears that NewID() is an exception. I just had to test this:

    CREATE TABLE dbo.TestFunctions(
     PrimaryKey int IDENTITY(1,1) NOT NULL,
     UniqueID uniqueidentifier NOT NULL,
     Random float NOT NULL,
     CONSTRAINT PK_TestFunctions PRIMARY KEY CLUSTERED (PrimaryKey ASC))

    And then:

    Insert into dbo.TestFunctions (UniqueID, Random)
    Select top 10
     NewID(),
     Rand()
    From Messages

    Where 'Messages' is any table with at least 10 records. Sure enough, UniqueID gets different values while Random gets the same value in every record.

    The fact that Rand() returns the same value for every record has been a source of frustration and innumerable forum threads over the years. As the article illustrates, using NewID() instead of Rand() would solve the problem posted in these forum threads.

    Then you'll be interested in 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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. šŸ˜‰

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

  • julian.fletcher

    SSCrazy

    Points: 2614

    Interesting, because I had thought that any function that took no parameters or a hard coded value parameter would only be evaluated once and would return the same value for every record in the returning record set. Examples of this are Rand() and GetDate(). So it appears that NewID() is an exception.

    The fact that Rand() returns the same value for every record has been a source of frustration and innumerable forum threads over the years.

    Absolutely!

    Also note that, while this approach is great in that you don't need to make any assumptions about the table's schema, it's pretty bad from a performance point of view - I think the whole table gets loaded in to memory. That might not be a problem in many cases, but if your table is large ...

  • Jeff Moden

    SSC Guru

    Points: 994661

    julian.fletcher - Monday, July 16, 2018 4:30 AM

    Interesting, because I had thought that any function that took no parameters or a hard coded value parameter would only be evaluated once and would return the same value for every record in the returning record set. Examples of this are Rand() and GetDate(). So it appears that NewID() is an exception.

    The fact that Rand() returns the same value for every record has been a source of frustration and innumerable forum threads over the years.

    Absolutely!

    Also note that, while this approach is great in that you don't need to make any assumptions about the table's schema, it's pretty bad from a performance point of view - I think the whole table gets loaded in to memory. That might not be a problem in many cases, but if your table is large ...

    If you build a whole table then, yes, the whole table will be in memory if it actually fits.  SQL Server cannot create a table without going through memory.

    Shifting gears a bit, you say "it's pretty bad from a performance point of view".  I've lost your train of thought.  What is "it" in this case?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. šŸ˜‰

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

  • julian.fletcher

    SSCrazy

    Points: 2614

    The "it" was the suggested solution:
    SELECT TOP 5 PERCENT *
    FROM Data.SalesByCountry
    ORDER BY NEWID()

    The table is already there - the task was to retrieve some records randomly.

  • Jeff Moden

    SSC Guru

    Points: 994661

    julian.fletcher - Monday, July 16, 2018 8:43 AM

    The "it" was the suggested solution:
    SELECT TOP 5 PERCENT *
    FROM Data.SalesByCountry
    ORDER BY NEWID()

    The table is already there - the task was to retrieve some records randomly.

    In that case, I totally agree! šŸ˜€

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. šŸ˜‰

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

Viewing 8 posts - 1 through 8 (of 8 total)

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