THE SQL Server Blog Spot on the Web

Welcome to - 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

    ZeroToNine (Digit) As (
        Select 0 As Digit
        Union All
        Select Digit + 1 From ZeroToNine Where Digit < 9),
    OneMillionRows (Number) As (
          Number = SixthDigit.Digit  * 100000 
                 + FifthDigit.Digit  *  10000 
                 + FourthDigit.Digit *   1000 
                 + ThirdDigit.Digit  *    100 
                 + SecondDigit.Digit *     10 
                 + FirstDigit.Digit  *      1 
            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)
    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 2, 2007 10:18 AM by AlbertoFerrari
Filed under: ,



Michelle Ufford said:

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

October 24, 2012 2:02 PM
New Comments to this post are disabled

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.
Privacy Statement