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

 Author Message Jeff Moden SSC Guru Group: General Forum Members Points: 507799 Visits: 44277 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: 507799 Visits: 44277 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: 507799 Visits: 44277 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: 507799 Visits: 44277 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 SSC-Forever Group: General Forum Members Points: 44197 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: 507799 Visits: 44277 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 Guru Group: General Forum Members Points: 52487 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: 507799 Visits: 44277 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 SSCertifiable Group: General Forum Members Points: 7954 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 SSCrazy Group: General Forum Members Points: 2936 Visits: 1052 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.