Generate a series of number

Below is a way to generate a series of number (10 records where the value starts from 1 to 10) with Common Table Expression (CTE):

with tb (ii)
as (
    select
        1 ii
    union all
        select ii + 1
        from tb
        where ii + 1 <= 10
)

select *
from tb

For the detailed explanation, check this out:

http://www.databasejournal.com/features/mssql/article.php/3502676/Common-Table-Expressions-CTE-on-SQL-2005.htm

When to use it

• Use CTE + UNION ALL to generate hierarchical result. For example, you have a discussion forum where it allows the members to post question (a thread) with many replies (many level of sub-threads).
• Reduce re-typing the same SELECT statement especially you are aggregating the data.