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

 Author Message Jeff Moden SSC Guru Group: General Forum Members Points: 334213 Visits: 42571 Comments posted to this topic are about the item Generating Test Data: Part 1 - Generating Random Integers and Floats --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 One Orange Chip Group: General Forum Members Points: 28067 Visits: 6431 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. And let me guess:`DECLARE @Range INT ,@StartValue DATETIME ,@EndValue DATETIMESELECT @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 ac2SELECT @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`:-) 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 Krtyknm SSC-Addicted Group: General Forum Members Points: 432 Visits: 375 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 paul.knibbs SSCertifiable Group: General Forum Members Points: 5758 Visits: 6240 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. GPO SSCrazy Group: General Forum Members Points: 2117 Visits: 1943 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. :-)One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.Bertrand Russell Bryant McClellan SSCommitted Group: General Forum Members Points: 1783 Visits: 546 Good idea. If you count all the posters except Jeff you are up to 10 cents:-D ------------Buy the ticket, take the ride. -- Hunter S. Thompson Jeff Moden SSC Guru Group: General Forum Members Points: 334213 Visits: 42571 Krtyknm (3/26/2012)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,KarthikThey 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. :-) --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: 334213 Visits: 42571 paul.knibbs (3/26/2012)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.Ah... I should have scrolled down a bit more before replying. Thanks for the cover, Paul! --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: 334213 Visits: 42571 GPO (3/26/2012)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.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. :-)That, notwithstanding, maybe we could get Al Gore to start a "No MVP left behind" project. :-D --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: 334213 Visits: 42571 G Bryant McClellan (3/26/2012)Good idea. If you count all the posters except Jeff you are up to 10 cents:-DHeh... that's more than Al Gore has given me so far. --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

## Permissions

 You can't post new topics. You can't post topic replies. You can't post new polls. You can't post replies to polls. You can't edit your own topics. You can't delete your own topics. You can't edit other topics. You can't delete other topics. You can't edit your own posts. You can't edit other posts. You can't delete your own posts. You can't delete other posts. You can't post events. You can't edit your own events. You can't edit other events. You can't delete your own events. You can't delete other events. You can't send private messages. You can't send emails. You can read topics. You can't vote in polls. You can't upload attachments. You can download attachments. You can't post HTML code. You can't edit HTML code. You can't post IFCode. You can't post JavaScript. You can post emoticons. You can't post or upload images.

##### Select a forum
 SQL Server 2016      SQL Server 2016 - Administration      SQL Server 2016 - Development and T-SQL SQL Server 2014      Administration - SQL Server 2014      Development - SQL Server 2014 SQL Server 2012      SQL 2012 - General      SQL Server 2012 - T-SQL SQL Server vNext      SQL Server 14 - Administration      SQL Server 14 - Development SQL Server 2008      SQL Server 2008 - General      T-SQL (SS2K8)      June 2007 CTP      Working with Oracle      July CTP      SQL Server Newbies      Security (SS2K8)      SQL Server 2008 High Availability      SQL Server 2008 Administration      Data Corruption (SS2K8 / SS2K8 R2)      SQL Server 2008 Performance Tuning Cloud Computing      SQL Azure - Development      SQL Azure - Administration      Amazon AWS and other cloud vendors      General Cloud Computing Questions Reporting Services      Reporting Services      Reporting Services 2005 Administration      Reporting Services 2005 Development      Reporting Services 2008/R2 Administration      Reporting Services 2008 Development      SSRS 2012      SSRS 2014      SSRS 2016 Programming      Connecting      General      SMO/RMO/DMO      XML      Service Broker      Powershell      Testing      TFS/Data Dude/DBPro      SSDT      Continuous Integration, Deployment, and Delivery      R Services and R Language Data Warehousing      Integration Services      Strategies and Ideas      Analysis Services      Data Transformation Services (DTS)      Performance Point      Data Mining      PowerPivot      R language      Machine Learning Database Design      Disaster Recovery      Design Ideas and Questions      Relational Theory      Hardware      Virtualization SQLServerCentral.com      Anything that is NOT about SQL!      Contests!      Editorials      SQLServerCentral.com Announcements      SQLServerCentral.com Website Issues      Suggestions      Tag Issues with Content      Podcast Feedback      SQLServerCentral.com Test Forum      Articles Requested SQL Server 2005      Administering      Backups      Business Intelligence      CLR Integration and Programming.      Data Corruption      Development      Working with Oracle      SQL Server 2005 Compact Edition      SQL Server 2005 General Discussion      SQL Server 2005 Security      SQL Server 2005 Strategies      SS2K5 Replication      SQL Server Express      SQL Server 2005 Performance Tuning      SQL Server 2005 Integration Services      T-SQL (SS2K5)      SQL Server Newbies SQL Server 7,2000      Administration      Backups      Data Corruption      General      Globalization      In The Enterprise      Working with Oracle      Security      Strategies      SQL Server Newbies      Service Packs      SQL Server CE      Performance Tuning      Replication      Sarbanes-Oxley      T-SQL      SQL Server Agent SQL Server and other platforms      MySQL      Oracle      PostgreSQL      DB2      SQL Server and Sharepoint Older Versions of SQL (v6.5, v6.0, v4.2)      Older Versions of SQL (v6.5, v6.0, v4.2) Career      Certification      Employers and Employees      Events      Job Postings      Resumes and Job Hunters      Presentations and Speaking      Retired Members Testing Center      Question of the Day (QOD)      SQL Server Security Skills Microsoft Access      Microsoft Access Products and Books      Third Party Products         CA         Extreme Technologies.         Innovartis         Embarcadero         SQL Sentry         Sonasoft         SQLCentric         Golden Gate Software         Lumigent         Red Gate Software         Quest Software         ApexSQL         Idera      Discussions about Books         Discuss Programming Books          Discuss XML Books          Discuss T-SQL Books          Discuss Data Warehousing Books          Discuss DTS Books          Discuss SQL Server 7.0 Books         Discuss SQL Server 2000 Books Notification Services      Administration Article Discussions Future Versions      SQL 12