syntaxhelp.com Your online cheat sheet!

arsing a delimited string using a Number Table

DECLARE @s VARCHAR(100)
SELECT @s = 'Apples,Oranges,Mangoes,Grapes'

;with cte AS (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY p1.Number) AS sr,
        number
    FROM master..spt_values p1
    WHERE p1.type = 'p' 
        AND p1.number between 1 AND LEN(@s) + 2
    AND SUBSTRING(',' + @s + ',',p1.number, 1) = ',' 
)
SELECT 
    SUBSTRING(' ' + @s, a.number + 1, b.number - a.number - 1) 
    AS Fruit
FROM cte a
INNER JOIN cte b ON b.sr = a.sr + 1
/*
Fruit
-----------------------------------------
Apples
Oranges
Mangoes
Grapes
*/

Creating a Number Table using a Recursive CTE

;WITH cte AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1 FROM cte
    WHERE Number < 500
)
SELECT Number FROM cte 
OPTION(MAXRECURSION 500)

Creating a Number Table using ROW_NUMBER() with a Large Table

;WITH cte AS (
    SELECT
        ROW_NUMBER() OVER(ORDER BY a.name) AS Number
    FROM sys.objects a 
    CROSS JOIN sys.objects b
)
SELECT * FROM cte
WHERE Number < 100

Creating a Number Table using ROW_NUMBER() and a few CTEs

WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)
SELECT N FROM NUM WHERE N <= 1000000;

Creating a Number Table using UNION ALL

WITH digits AS (
     SELECT 0 as Number
     UNION SELECT 1
     UNION SELECT 2
     UNION SELECT 3
     UNION SELECT 4
     UNION SELECT 5
     UNION SELECT 6
     UNION SELECT 7
     UNION SELECT 8
     UNION SELECT 9
)
SELECT 
    (hundreds.Number * 100) + 
    (tens.Number * 10) +
     ones.Number as Number
FROM digits as hundreds
CROSS JOIN digits as tens
CROSS JOIN digits as ones 
ORDER BY Number

Creating a Tally Table with 1 million rows

--===== Create and populate the Numbers table on the fly.
     -- The IDENTITY makes "N" a NOT NULL column for use as a PK.
 SELECT TOP 1000001
        IDENTITY(INT,0,1) AS N
   INTO dbo.Tally
   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2