THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

Generate huge test tables using CTE

Sometimes I need to have a huge table to make performance tests and I want to create the table easily. I found a simple way of producing these test data using CTE in a creative way and decided to post it so I can have it at hand when needed and maybe somebody will find it useful.

The trick is to have a CTE Select that generates numbers from 0 to 9 and then cross join it with itself several times composing the numbers you want. The sample generates a one million row but can be easily changed to fit your needs

With 
    ZeroToNine (Digit) As (
        Select 0 As Digit
        Union All
        Select Digit + 1 From ZeroToNine Where Digit < 9),
    OneMillionRows (Number) As (
        Select 
          Number = SixthDigit.Digit  * 100000 
                 + FifthDigit.Digit  *  10000 
                 + FourthDigit.Digit *   1000 
                 + ThirdDigit.Digit  *    100 
                 + SecondDigit.Digit *     10 
                 + FirstDigit.Digit  *      1 
        From
            ZeroToNine As FirstDigit  Cross Join
            ZeroToNine As SecondDigit Cross Join
            ZeroToNine As ThirdDigit  Cross Join
            ZeroToNine As FourthDigit Cross Join
            ZeroToNine As FifthDigit  Cross Join
            ZeroToNine As SixthDigit)
Select 
    COD_Test = Number,
    Test     = Replicate ('A', 100) 
From OneMillionRows 
Order By Number

Removing the ORDER BY clause to the final SELECT you will have unordered rows, this can be useful if you need – for example – to test a SORT component.

Published Monday, July 02, 2007 10:18 AM by AlbertoFerrari
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Michelle Ufford said:

This code snippet was very helpful and, best of all, fast! Thank you, Alberto! :)

October 24, 2012 2:02 PM

Leave a Comment

(required) 
(required) 
Submit

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement