Create a Numbers Table using SQL Common Table Expressions

7 Nov

The concept of a CTE, or common table expression, is something new in SQL 2005, and it is hugely powerful for things such as traversing trees or hierarchies, among other things.

You can use a CTE to generate an ad-hoc Numbers Table.  Try this:

<span class="kwrd">WITH</span> Numbers(n)<br /><span class="kwrd">AS</span><br />(<br /><span class="kwrd">SELECT</span> 1 <span class="kwrd">AS</span> n<br /><span class="kwrd">UNION</span> <span class="kwrd">ALL</span><br /><span class="kwrd">SELECT</span> (n + 1) <span class="kwrd">AS</span> n<br /><span class="kwrd">FROM</span> Numbers<br /><span class="kwrd">WHERE</span><br />n &lt; 1000<br />)<br /><span class="kwrd">SELECT</span> n <span class="kwrd">FROM</span> Numbers <br /><span class="kwrd">OPTION</span>(MAXRECURSION 1000) 

That will generate numbers to 1000.  Change the comparison expression, and raise up the max recursion depth, to go to a higher number.  Or, instead of using the CTE in your SQL, you could just do a SELECT INTO and dump it into a table.

If your not sure why you would want a numbers table, just think about it a bit, or go Google!  A numbers table is fantastically useful for all sorts of tasks in a database, including parsing text, joining against to create multiples, and more.

Tweet about this on TwitterShare on Google+Share on FacebookShare on RedditEmail this to someoneShare on LinkedInShare on StumbleUpon

Leave a Reply

Your email address will not be published. Required fields are marked *