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.