SQLServerCentral / Discuss Content Posted by Jeff Moden / Article Discussions / Article Discussions by Author / Generating Test Data: Part 2 - Generating Sequential and Random Dates / Latest Posts

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Mon, 17 Dec 2012 18:13:31 GMT

Thanks, Boriskey :blush: I really appreciate the feedback. I aim to please... I sometimes miss but I'm always aiming. :-D

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

boriskey — Mon, 17 Dec 2012 07:31:52 GMT

[quote][b]Jeff Moden (12/16/2012)[/b][hr]Here's another way... same idea, though.[/quote]awesome, it will work! thanks for the quick response, Jeff! and I am going to use this opportunity to say BIG THANK YOU for all your articles and knowledge you shared with us!

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Sun, 16 Dec 2012 21:13:56 GMT

Here's another way... same idea, though.[code="sql"]DROP TABLE #MyHead; SELECT TOP (1000) ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), RandomColor = CASE RandomColor# WHEN 1 THEN 'Red' WHEN 2 THEN 'Green' ELSE 'Yellow' END INTO #MyHead FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2 CROSS APPLY (SELECT ABS(CHECKSUM(NEWID()))%3) ca (RandomColor#);--===== Show the distribution SELECT RandomColor, COUNT(*) FROM #MyHead GROUP BY RandomColor ORDER BY RandomColor;[/code]

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Sun, 16 Dec 2012 21:09:01 GMT

No... not from a sub-query. At least I haven't figured out a way to do it with a TOP 1/ORDER BY like that.You could do it using a CASE function based on ABS(CHECKSUM(NEWID()))%3 but that will actually come up with 4 values because, since NEWID() isn't deterministic, the CASE function will recalculate each and every WHEN even if you use CASE ABS(CHECKSUM(NEWID()))%3. You could then change the formula to ABS(CHECKSUM(NEWID()))%2 and use ELSE but that will give you an uneven distribution.So, the only thing to do is to gen the numbers that control the color separately and then CASE that number. Here's one way of doing that in a single query.[code="sql"]DROP TABLE #MyHead;WITHcteRandom AS( --=== Generate the random number first SELECT TOP (1000) ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), RandomColor# = ABS(CHECKSUM(NEWID()))%3 FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2) --=== Now, pick the color according to the generated number SELECT ID, RandomColor = CASE RandomColor# WHEN 1 THEN 'Red' WHEN 2 THEN 'Green' ELSE 'Yellow' END INTO #MyHead FROM cteRandom;--===== Show the distribution SELECT RandomColor, COUNT(*) FROM #MyHead GROUP BY RandomColor ORDER BY RandomColor[/code]

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

boriskey — Sun, 16 Dec 2012 19:15:42 GMT

Jeff, thanks for the great articles (part 1 and part 2 and look forward to part3).Is there a way to pick a random value (like a color) from a subquery? here is what I tried but getting the same value on every run:SELECT TOP (100)ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as ID, --Sequential number from 1 to ..(SELECT top 1 color FROM ( VALUES (0,'Red'),(1,'Green'),(2,'Yellow') ) colors(id,color) ORDER BY NEWID()) as RandomColor,FROM sys.all_columns ac1CROSS JOIN sys.all_columns ac2but if I run this piece alone, I am getting different colors:SELECT top 1 color FROM ( VALUES (0,'Red'),(1,'Green'),(2,'Yellow') ) colors(id,color) ORDER BY NEWID()

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

WayneS — Sun, 01 Jul 2012 17:34:08 GMT

Great article Jeff.One thing to mention... in the article, you pointed out that you cannot directly add / subtract a number to the new DATE data time. This applies to all of the new date data types: DATETIME2, DATETIMEOFFSET and TIME as well as DATE.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Sun, 20 May 2012 15:35:16 GMT

[quote][b]Dev (5/20/2012)[/b][hr]Nice Article Jeff!!![/quote]Thanks, Dev. I appreciate both the read and the feedback.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Sun, 20 May 2012 15:34:32 GMT

[quote][b]GPO (5/19/2012)[/b][hr]Now that we're all up-to-speed on generating test data, ;-) you know what would be trez cool (not suggesting that Jeff should have to do it, but it would be great if it existed)? A step-by-step guide to setting up an empirical test environment. It seems to me that there are too many traps for us new player, that will lead us to incorrectly conclude that method A is better/worse/no different to method B.Issues to consider, for example: :: I've read recently that you shouldn't conclude that because a query takes X seconds to bring the data to your screen, that it's a fair representation of how long the query took to run. Most of the "execution" time could simply be shipping a million rows of data over the network. The workaround might be to run the data into a temp table or a table variable... or... something (he said knowing he was out of his depth) :: How should we treat the cache and buffers? :: How do we set up a timer. I simply set variables to getdate() at the start and end of what I'm trying to test, and datediff them. Is this reasonable? :: What are the pitfalls to taking the execution plan's "cost relative to batch" and "estimated subtree cost" literally? I've seen it wildly inaccurate, and not just because of outdated statistics and so on. It's often because it can't accurately estimate the cost of a scalar function, for example. :: I've used Adam Machanic's SQLQueryStress tool before because it seems that it can give you an idea of how the query will perform with multiple threads and iterations with a variety of parameters.[/quote]You should see the nasty problems that come up with supposedly reliable things like SET STATISTICS TIME ON. I'm mostly convinced that the errors there are the reason the supposed best practice of avoiding scalar UDFs exists. I say, "It Depends" and have "guts" of an "SQL Spackle" article setup for it.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Dev — Sun, 20 May 2012 04:32:10 GMT

Nice Article Jeff!!!

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

GPO — Sat, 19 May 2012 20:54:31 GMT

Now that we're all up-to-speed on generating test data, ;-) you know what would be trez cool (not suggesting that Jeff should have to do it, but it would be great if it existed)? A step-by-step guide to setting up an empirical test environment. It seems to me that there are too many traps for us new player, that will lead us to incorrectly conclude that method A is better/worse/no different to method B.Issues to consider, for example: :: I've read recently that you shouldn't conclude that because a query takes X seconds to bring the data to your screen, that it's a fair representation of how long the query took to run. Most of the "execution" time could simply be shipping a million rows of data over the network. The workaround might be to run the data into a temp table or a table variable... or... something (he said knowing he was out of his depth) :: How should we treat the cache and buffers? :: How do we set up a timer. I simply set variables to getdate() at the start and end of what I'm trying to test, and datediff them. Is this reasonable? :: What are the pitfalls to taking the execution plan's "cost relative to batch" and "estimated subtree cost" literally? I've seen it wildly inaccurate, and not just because of outdated statistics and so on. It's often because it can't accurately estimate the cost of a scalar function, for example. :: I've used Adam Machanic's SQLQueryStress tool before because it seems that it can give you an idea of how the query will perform with multiple threads and iterations with a variety of parameters.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Gail Wanabee — Sat, 19 May 2012 19:41:12 GMT

Thanks. I'm looking forward to it, as is my company's software development staff. LC

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Sat, 19 May 2012 18:33:21 GMT

[quote][b]Lee Crain (5/4/2012)[/b][hr]Jeff, I may have missed it glancing through all of the posts. When do you plan to produce Part 3?Thanks for the effort you expended on such excellent documentation.LC[/quote]Apologies for the late response. It's one of those things where you say to yourself that you'll answer that one "tomorrow". I'd intended to be done with Part 3 by now but have barely scratched the surface of it. I'll going to try to get it done this week and submit it next weekend. It takes about 4 to 6 weeks after submital for an article to come out.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Gail Wanabee — Fri, 04 May 2012 16:08:16 GMT

Jeff, I may have missed it glancing through all of the posts. When do you plan to produce Part 3?Thanks for the effort you expended on such excellent documentation.LC

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Tue, 01 May 2012 05:04:28 GMT

Heh... XXX... did you really mean "Part 30"?Random gaps and islands are easy although known gaps and islands make life a whole lot easier test wise. Just build a wad of sequential dates and randomly delete a percentage of them.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

dwain.c — Mon, 30 Apr 2012 22:00:21 GMT

[quote][b]Jeff Moden (4/30/2012)[/b][hr][quote][b]Samrat Bhatnagar (4/28/2012)[/b][hr]These two part series were really useful. Thanks.Any suggestions on how to generate test data for following scenarios:1. Two tables linked using PK-FK relationship e.g. Product Category and Product Subcategory2. Self Referential Tables like the Employee table with EmployeeId, ManagerId, <Other employee details>3. Using the master tables in 1, 2 generate a table that has ProductFK, EmployeeFK, <Some data> as in a Data warehouse.[/quote]Sure. I might be able to include some of that in part 3.[/quote]How about something in part XXX about generating data with random gaps and islands. :-)

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Mon, 30 Apr 2012 06:07:44 GMT

[quote][b]Samrat Bhatnagar (4/28/2012)[/b][hr]These two part series were really useful. Thanks.Any suggestions on how to generate test data for following scenarios:1. Two tables linked using PK-FK relationship e.g. Product Category and Product Subcategory2. Self Referential Tables like the Employee table with EmployeeId, ManagerId, <Other employee details>3. Using the master tables in 1, 2 generate a table that has ProductFK, EmployeeFK, <Some data> as in a Data warehouse.[/quote]Sure. I might be able to include some of that in part 3.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

bitbucket-25253 — Sat, 28 Apr 2012 16:02:26 GMT

[quote][b]Jeff Moden (4/26/2012)[/b][hr][quote][b]TheSQLGuru (4/25/2012)[/b][hr]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![/quote]If I understand correctly, those are pretty well summarized in the last two sections of the article. Is that what you want as a downloadable file?[/quote]Certainly, am too lazy to do a cut and paste to my Sandbox DB...

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Samrat Bhatnagar — Sat, 28 Apr 2012 14:26:28 GMT

These two part series were really useful. Thanks.Any suggestions on how to generate test data for following scenarios:1. Two tables linked using PK-FK relationship e.g. Product Category and Product Subcategory2. Self Referential Tables like the Employee table with EmployeeId, ManagerId, <Other employee details>3. Using the master tables in 1, 2 generate a table that has ProductFK, EmployeeFK, <Some data> as in a Data warehouse.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Thu, 26 Apr 2012 18:01:21 GMT

[quote][b]TheSQLGuru (4/25/2012)[/b][hr]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![/quote]If I understand correctly, those are pretty well summarized in the last two sections of the article. Is that what you want as a downloadable file?

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Thu, 26 Apr 2012 17:58:42 GMT

I see what you mean. Same principle... just harder for some folks to see. Thanks, Craig.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Cadavre — Thu, 26 Apr 2012 08:11:54 GMT

[quote][b]Jeff Moden (4/26/2012)[/b][hr][quote][b]Cadavre (4/26/2012)[/b][hr]Nicely explained Jeff.I may have to "borrow" your method of generating random DATETIME data, my method is more difficult to understand when people glance at it. :-D[/quote]Borrow away. These aren't my methods. They pretty well standard for folks that have been using NEWID() for such things over the years because they fall into the classic random number mathematical formulas.If you don't mind, could you post your method? It's always interesting to see how others do things.[/quote]Certainly. Generally, when I need "random" datetime data I go with this: -[code="sql"]IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGINDROP TABLE #testEnvironmentEND--1,000,000 "Random" rows of dataSELECT TOP 1000000 IDENTITY(INT,1,1) AS ID, RAND(CHECKSUM(NEWID())) * 366 /*(Number of days)*/ + CAST('2000' AS DATETIME) /*(Start date, e.g. '2000-01-01 00:00:00'*/ AS randomDateINTO #testEnvironmentFROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;[/code]If instead I want "random" date, I normally go with this: -[code="sql"]IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGINDROP TABLE #testEnvironmentEND--1,000,000 "Random" rows of dataSELECT TOP 1000000 IDENTITY(INT,1,1) AS ID, DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 366 /*(Number of days)*/) + 1),CAST('2000' AS DATE) /*(Start date, e.g. '2000-01-01*/) AS randomDateINTO #testEnvironmentFROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;[/code]On an internal wiki-page at work, I long since added a page with a brief explanation of how to create pseudo-random data. The script looks like this: -[code="sql"]--Standard TestEnvironment of 1,000,000 rows of random-ish dataIF object_id('tempdb..#testEnvironment') IS NOT NULLBEGINDROP TABLE #testEnvironment;END;--1,000,000 Random rows of dataSELECT TOP 1000000 IDENTITY(INT,1,1) AS ID, RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDateTime,--SQL SERVER 2008 ONLY!! ONLY FOR USE ON 9.0 AND ABOVEDATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 366) + 1),CAST('2000' AS DATE)) AS randomDate,ABS(CHECKSUM(NEWID())) AS randomBigInt,(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,RAND(CHECKSUM(NEWID())) AS randomTinyDec,RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoneyINTO #testEnvironmentFROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;[/code]

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Thu, 26 Apr 2012 07:23:31 GMT

[quote][b]GPO (4/25/2012)[/b][hr]The spam people clearly target the articles that everyone is going to read...[/quote]It's ironic that I have the SPAM people to thank for such a nice compliment. Thanks, GPO.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Thu, 26 Apr 2012 07:19:19 GMT

[quote][b]Cadavre (4/26/2012)[/b][hr]Nicely explained Jeff.I may have to "borrow" your method of generating random DATETIME data, my method is more difficult to understand when people glance at it. :-D[/quote]Borrow away. These aren't my methods. They pretty well standard for folks that have been using NEWID() for such things over the years because they fall into the classic random number mathematical formulas.If you don't mind, could you post your method? It's always interesting to see how others do things.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Thu, 26 Apr 2012 07:15:35 GMT

[quote][b]TheSQLGuru (4/25/2012)[/b][hr]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![/quote]Heh... stealing an idea from Lotus isn't my idea of a laudable defense. ;-)

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Cadavre — Thu, 26 Apr 2012 07:00:12 GMT

Nicely explained Jeff.I may have to "borrow" your method of generating random DATETIME data, my method is more difficult to understand when people glance at it. :-D

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Thu, 26 Apr 2012 06:57:25 GMT

[quote][b]GPO (4/25/2012)[/b][hr]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. [/quote]"More please?" is the normal question after any such introductory article and I thank you for your thoughtful feedback.I've done such things in more of a stepped fashion but not in a nice, smooth sinusoidal fashion. I suppose that it just a matter of making smaller steps. I'll have to think about that. Thank you, again, for the feedback.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Thu, 26 Apr 2012 06:52:03 GMT

[quote][b]dwain.c (4/25/2012)[/b][hr]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.[/quote]There's quite a bit more as to how they actually work behind the scenes but few people ever need to go there... including me. ;-)Thanks for the feedback, Dwain.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Thu, 26 Apr 2012 06:49:46 GMT

[quote][b]bitbucket-25253 (4/24/2012)[/b][hr]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.[/quote]As always, very good hearing from you, ol' friend. Thanks for stopping by, Ron.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

GPO — Wed, 25 Apr 2012 19:47:57 GMT

The spam people clearly target the articles that everyone is going to read...

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

dwain.c — Wed, 25 Apr 2012 19:44:18 GMT

Jeff - What is it about you that attracts spam so well? Your cologne perhaps?

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

Scott Levy — Wed, 25 Apr 2012 16:56:16 GMT

Just to leap to Excel's defence there is a reason that it thinks 1900 is a leap year.[url=http://www.joelonsoftware.com/items/2006/06/16.html]Joel Spolsky can across it while working on Excel in 1991.[/url]

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

TheSQLGuru — Wed, 25 Apr 2012 08:56:59 GMT

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!

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

dwain.c — Wed, 25 Apr 2012 07:08:39 GMT

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.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

GPO — Wed, 25 Apr 2012 06:01:07 GMT

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.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

dwain.c — Wed, 25 Apr 2012 00:04:28 GMT

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.

RE: Generating Test Data: Part 2 - Generating Sequential and Random Dates

bitbucket-25253 — Tue, 24 Apr 2012 21:44:52 GMT

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.

Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden — Tue, 24 Apr 2012 21:40:48 GMT

Comments posted to this topic are about the item [B]Generating Test Data: Part 2 - Generating Sequential and Random Dates[/B]