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.