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:

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.

Leave a Reply

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