Generating Test Data: Part 1 - Generating Random Integers and Floats

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Generating Test Data: Part 1 - Generating...

59 posts, Page 1 of 6

1 2 3 4 5 »»»

Generating Test Data: Part 1 - Generating Random Integers and Floats

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Sunday, March 25, 2012 5:26 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771

Comments posted to this topic are about the item Generating Test Data: Part 1 - Generating Random Integers and Floats

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

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 #1272409

dwain.c

Posted Monday, March 26, 2012 12:20 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 2,340, Visits: 3,165

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
	,@StartValue	DATETIME
	,@EndValue		DATETIME

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

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 #1272459

Krtyknm

Posted Monday, March 26, 2012 3:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 2:59 AM
Points: 137, Visits: 314

Hi Jeff,

Nice Article, Keep going.

I have a question on sys tables, most of them using the Sys.tables for generating random numbers. Assume that developers don't have an access to System tables, then how can they get the random numbers.

Thanks,
Karthik

Post #1272507

paul.knibbs

Posted Monday, March 26, 2012 4:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 1,257, Visits: 4,255

Krtyknm (3/26/2012)
I have a question on sys tables, most of them using the Sys.tables for generating random numbers. Assume that developers don't have an access to System tables, then how can they get the random numbers.

He's only using a system table (or view, strictly speaking) because of the number of rows it has--any table with a decent number of rows will work, whether it's a tally table or one of your main data tables.

Post #1272528

GPO

Posted Monday, March 26, 2012 4:38 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:30 AM
Points: 648, Visits: 1,302

This article was called "Generating Test Data: Part 1 - Generating Random Integers and Floats" It should have been called "Generating Test Data: Part 1 - A Way Cool Compendium of Well Explained El Neato T-SQL Techniques"

Just one minor gripe - not about the article - about us, the SQL Server community. I know times are tough but we should all be ashamed of ourselves for allowing a true luminary of the T-SQL world to slip into such penury that he is only able to afford the sort of machine that my kids wouldn't (well probably couldn't) watch YouTube on. What say we pass the hat around and get a natty 2006 machine off ebay for Jeff? Maybe we could start up a One Laptop Per MVP project.

Smile

One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell

Post #1272532

G Bryant McClellan

Posted Monday, March 26, 2012 5:57 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:13 AM
Points: 799, Visits: 422

Good idea. If you count all the posters except Jeff you are up to 10 cents BigGrin

------------
Buy the ticket, take the ride. -- Hunter S. Thompson

Post #1272570

Jeff Moden

Posted Monday, March 26, 2012 6:17 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771

Krtyknm (3/26/2012)

They can use virtually any table. Any table with just 1000 rows will allow them to build a million row table using the Cross Join.

That, notwithstanding, in such situations and like I said in the article, I'll build a Tally table for them. My Tally tables usually have 11000 rows in them so I can use it to build 30 years of dates. That's enough for a Cross Join to build a million rows... or 121 million if they want. Smile

--Jeff Moden

Post #1272579

Jeff Moden

Posted Monday, March 26, 2012 6:20 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771

paul.knibbs (3/26/2012)

Ah... I should have scrolled down a bit more before replying. Thanks for the cover, Paul!

--Jeff Moden

Post #1272582

Jeff Moden

Posted Monday, March 26, 2012 6:25 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771

GPO (3/26/2012)

That's a much cooler title! Thanks for the great feedback.

I actually do have a more modern HP G71 laptop that comparatively screams and will do parallelism, etc but I actually like my ol' war horse. If I can make something run fast on it, I know you folks with "real" machines are going to love it. Smile

That, notwithstanding, maybe we could get Al Gore to start a "No MVP left behind" project. BigGrin

--Jeff Moden