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.