Tips: Generate Sequence Number 1 - 100 (No Physical Table, No Loop)

How to generate sequence number 1 through 100 without any physical(System, User defined) table, Loop.

We can do that, In SQL Server 2005, 2008, 2008 R2 we have new feature called Recursive CTE - Common Table Expression.

CTE Syntax:
;WITH CTEName
AS
(
SELECT Statement1
UNION ALL
SELECT Statement2
)
SELECT * FROM CTEName [Query Hint]

Sample:
WITH CTEs
AS
(
SELECT 1 AS Sequence
UNION ALL
SELECT Sequence + 1 FROM CTEs WHERE Sequence < 100
)
SELECT * FROM CTEs

Result:
 
 
 
 
 
 
 
 
 
 
 
Internally, Optimizer uses Nested Loop, But we are not using any loop in the script.

1 comment:

  1. Another approach.
    Generating sequence no with user defined tables
    Hope its right


    create table t
    (
    ids int
    )

    insert into t(ids)
    select isnull(max(ids),0) + 1 from t
    go 100

    ReplyDelete