Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

Generating Test Data: Part 1 - Generating Random Integers and Floats Expand / Collapse
Author
Message
Posted Tuesday, March 27, 2012 7:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
Michael Valentine Jones (3/27/2012)
dwain.c (3/26/2012)
Jeff,

The following thought occurred to me last night but I didn't get a chance to test it until this morning.

Isn't the modulo function designed to always return a positive integer?

Hence, in this part of your data generator, I don't believe you need to use ABS:

SomeRandomInteger =  CHECKSUM(NEWID()) % @Range + @StartValue

I didn't try it for a million rows but the first 200 came up all as positives.


It's pretty easy to show that the modulus operator will return a negative number:

select x = -1111%100

Results:
x           
-----------
-11


Gosh. I'm not sure how I missed Dwain's question. Thanks for the cover, Michael.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1274004
Posted Tuesday, March 27, 2012 7:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
dwain.c (3/26/2012)
Jeff,

The following thought occurred to me last night but I didn't get a chance to test it until this morning.

Isn't the modulo function designed to always return a positive integer?

Hence, in this part of your data generator, I don't believe you need to use ABS:

SomeRandomInteger =  CHECKSUM(NEWID()) % @Range + @StartValue

I didn't try it for a million rows but the first 200 came up all as positives.


Apologies, Dwain. I'm not sure how I missed your question on this.

As Michael demonstrated, Modulo will return a negative number if the "Dividend" of the division is negative.

SELECT -1111 % 100,
1111 %-100,
-1111 %-100

----------- ----------- -----------
-11 11 -11

(1 row(s) affected)


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1274005
Posted Tuesday, March 27, 2012 7:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
Scott Abrants (3/26/2012)
Excellent post! Great examples, great code, and easy to follow!
Nice job Jeff!


Thanks for the feedback, Scott. I appreciate you stopping by.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1274008
Posted Tuesday, March 27, 2012 7:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
ALZDBA (3/26/2012)
Great extrapolation of the KISS principle, Jeff.

Need it to be sead ... I LOVE IT



Thanks for the feedback, Johan. I agree... the simpler, the better.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1274009
Posted Tuesday, March 27, 2012 7:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
dwain.c (3/26/2012)
--------------------------------------------------------------------------------
Jeff,

The following thought occurred to me last night but I didn't get a chance to test it until this morning.

Isn't the modulo function designed to always return a positive integer?

Hence, in this part of your data generator, I don't believe you need to use ABS:

SomeRandomInteger = CHECKSUM(NEWID()) % @Range + @StartValue
I didn't try it for a million rows but the first 200 came up all as positives.


Apologies, Dwain. I'm not sure how I missed your question on this.


I almost wish you had missed it because obviously my thinking cap wasn't on when I posted it.

There still might be a way to use the negative return value to avoid the ABS by adjusting the range. I'm not sure the complication is worth investigating though.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1274010
Posted Thursday, March 29, 2012 9:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
Spam reported. And, no... $33 for a pair of jeans is no way to thank anyone.

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1275489
Posted Friday, March 30, 2012 3:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
Playing catchup on being underwater in a project for the last few weeks. Article looks great Jeff, and I'm sure will be very valuable. Nice Work!


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1276072
Posted Friday, March 30, 2012 9:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
Cool... 5 birds with 1 stone. SPAM reported.

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1276136
Posted Sunday, April 1, 2012 2:12 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:50 AM
Points: 1,602, Visits: 5,652
Jeff Moden (3/30/2012)
Cool... 5 birds with 1 stone. SPAM reported.


Well, strictly speaking it's the same bird 5 times...and what is it with this thread and spam, anyway? You must be particularly attractive to them, Jeff.
Post #1276411
Posted Saturday, April 21, 2012 7:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:22 PM
Points: 537, Visits: 771
Another brilliant article from Jeff. Easy and fun read (not very often you can say this about a technical text). Can't wait for Part 2.


Post #1287640
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse