Inside Microsoft® SQL Server® 2008: T-SQL Querying

Auxiliary Table of Numbers

An auxiliary table of numbers is a very powerful tool that I often use in my solutions. So I decided to dedicate a section in this chapter to it. In this section, I’ll simply describe the concept and the methods used to generate such a table. I’ll refer to this auxiliary table throughout the book and demonstrate many of its applications.

An auxiliary table of numbers (call it Nums) is simply a table that contains the integers between 1 and n for some (typically large) value of n. I recommend that you create a permanent Nums table and populate it with as many values as you might need for your solutions.

The code in Example 6-3 demonstrates how to create such a table containing 1,000,000 rows. Of course, you might want a ...