<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Jeff Moden
»
Generating Test Data: Part 2 - Generating...
37 posts, Page 1 of 4
1
2
3
4
»
»»
Generating Test Data: Part 2 - Generating Sequential and Random Dates
Rate Topic
Display Mode
Topic Options
Author
Message
Jeff Moden
Jeff Moden
Posted Tuesday, April 24, 2012 9:40 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893,
Visits: 26,770
Comments posted to this topic are about the item
Generating Test Data: Part 2 - Generating Sequential and Random Dates
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1289524
bitbucket-25253
bitbucket-25253
Posted Tuesday, April 24, 2012 9:44 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 5,100,
Visits: 20,195
Thanks Jeff, another great article with code that everyone should add to their sandbox DB. And of course, use same, to test if an item is ready for production.
If everything seems to be going well, you have obviously overlooked something.
Ron
Please help us, help you -before posting a question please
read
Before posting a performance problem please
read
Post #1289525
dwain.c
dwain.c
Posted Wednesday, April 25, 2012 12:04 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 2,340,
Visits: 3,163
You da man Jeff! Another great article!
I've seen quite a few forum posts recently where your discussion of how DATETIMEs actually work would have assisted the posters had they understood this fundamental concept.
No loops! No CURSORs! No RBAR! Hoo-uh!
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1289552
GPO
GPO
Posted Wednesday, April 25, 2012 6:01 AM
SSChasing Mays
Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 648,
Visits: 1,302
Superbly balanced article. But in true Oliver Twist fashion, "Can I have some more?"
In the real-world datetime data I've dealt with, they often follow roughly cyclical patterns, not pure random patterns. For example, if you look at say ambient temperature in the town in which you live, over time it might approximate a sort-of sine wave from day to day, but also the larger sine wave of seasonal change. Similarly, if you look at something like Emergency Room attendances, there is a daily, weekly, and seasonal repeating pattern of attendance numbers. If you want to generate test ER attendance datetimes, you don't want them to be strictly random, because in the long run, you'll generate about the same number of ER attendances (for example) for 3:00AM as you will for 3:00PM. You instead, want it to be random within certain parameters. If you knew the mean and standard deviation of the number of attendances of the 24 hours of the day, by the seven days of the week, by the 4 (in my case) seasons of the year (672 rows of reference data), I'm wondering whether you could combine that knowledge with Jeff's techniques to generate test data that closely approximates the patterns seen in reality.
One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1289769
dwain.c
dwain.c
Posted Wednesday, April 25, 2012 7:08 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 2,340,
Visits: 3,163
GPO - Not always as easy as we would like.
I have a vague recoolection that you can convert uniform random numbers to another distribution by multiplying by the inverse of the probability density function.
Poisson distributions are challenging because the algorithms I've seen for generating Poisson random numbers (hospital arrivals for example) use an iterative approach.
Sinusoidal functions might be a little easier but may be calculated differently. For example, you may expect temperatures to fluctuate randomly around a + or - band on the sinusoidal wave.
No loops! No CURSORs! No RBAR! Hoo-uh!
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1289828
TheSQLGuru
TheSQLGuru
Posted Wednesday, April 25, 2012 8:56 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 9:01 PM
Points: 3,572,
Visits: 5,108
Hey Jeff, can you put a downloadable file with the relevant operational code parts of the post? Thanks in advance, and wonderful stuff as always!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1289959
Scott Levy
Scott Levy
Posted Wednesday, April 25, 2012 4:56 PM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, January 15, 2013 5:10 PM
Points: 9,
Visits: 48
Just to leap to Excel's defence there is a reason that it thinks 1900 is a leap year.
Joel Spolsky can across it while working on Excel in 1991.
Post #1290331
dwain.c
dwain.c
Posted Wednesday, April 25, 2012 7:44 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:01 PM
Points: 2,340,
Visits: 3,163
Jeff - What is it about you that attracts spam so well? Your cologne perhaps?
No loops! No CURSORs! No RBAR! Hoo-uh!
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1290377
GPO
GPO
Posted Wednesday, April 25, 2012 7:47 PM
SSChasing Mays
Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 648,
Visits: 1,302
The spam people clearly target the articles that everyone is going to read...
One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1290379
Jeff Moden
Jeff Moden
Posted Thursday, April 26, 2012 6:49 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893,
Visits: 26,770
bitbucket-25253 (4/24/2012)
Thanks Jeff, another great article with code that everyone should add to their sandbox DB. And of course, use same, to test if an item is ready for production.
As always, very good hearing from you, ol' friend. Thanks for stopping by, Ron.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1290640
« Prev Topic
|
Next Topic »
37 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
