Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Generating Test Data: Part 1 - Generating Random Integers and Floats Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, March 27, 2012 7:04 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 4:29 PM Points: 42,055, Visits: 39,440
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1274004
 Posted Tuesday, March 27, 2012 7:11 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 4:29 PM Points: 42,055, Visits: 39,440
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1274005
 Posted Tuesday, March 27, 2012 7:27 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 4:29 PM Points: 42,055, Visits: 39,440
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1274008
 Posted Tuesday, March 27, 2012 7:29 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 4:29 PM Points: 42,055, Visits: 39,440
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1274009
 Posted Tuesday, March 27, 2012 7:29 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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 + @StartValueI 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1274010
 Posted Thursday, March 29, 2012 9:23 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 4:29 PM Points: 42,055, Visits: 39,440
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1275489
 Posted Friday, March 30, 2012 3:43 PM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, September 23, 2015 3:34 PM Points: 5,467, Visits: 7,660
 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 FarrellNever 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 NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA
Post #1276072
 Posted Friday, March 30, 2012 9:55 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 4:29 PM Points: 42,055, Visits: 39,440
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1276136
 Posted Sunday, April 1, 2012 2:12 PM
 SSCommitted Group: General Forum Members Last Login: Friday, October 14, 2016 2:57 AM Points: 1,833, Visits: 6,210
 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 Group: General Forum Members Last Login: Friday, December 2, 2016 7:50 PM Points: 556, Visits: 972
 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

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.