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

 Author Message Jeff Moden SSC Guru Group: General Forum Members Points: 214596 Visits: 41979 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 214596 Visits: 41979 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 214596 Visits: 41979 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 214596 Visits: 41979 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs dwain.c SSCoach Group: General Forum Members Points: 17777 Visits: 6431 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 Jeff Moden SSC Guru Group: General Forum Members Points: 214596 Visits: 41979 Spam reported. And, no... \$33 for a pair of jeans is no way to thank anyone. --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Evil Kraig F SSC-Insane Group: General Forum Members Points: 20561 Visits: 7660 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 Jeff Moden SSC Guru Group: General Forum Members Points: 214596 Visits: 41979 Cool... 5 birds with 1 stone. SPAM reported. ;-) --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs paul.knibbs SSCarpal Tunnel Group: General Forum Members Points: 4210 Visits: 6240 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. :-D Misha_SQL SSCommitted Group: General Forum Members Points: 1654 Visits: 1010 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.