--== TALLY ==--
SELECT N
FROM Tally
WHERE N > 0 AND N <= 100;

--== WHILE LOOP ==--
DECLARE @HOLDER TABLE (N INT);
DECLARE @COUNTER INT = 0;

WHILE @COUNTER < 100
BEGIN
    SET @COUNTER = @COUNTER + 1;
    INSERT INTO @HOLDER
    SELECT @COUNTER;
END

SELECT N
FROM @HOLDER;

--== RECURSIVE CTE ==--
WITH CTE(N) AS (
SELECT 1 
UNION ALL
SELECT N+1 
FROM CTE
WHERE N+1 <= 100)
SELECT N
FROM CTE;

--== CASCADING CROSS JOIN ==--
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE X, CTE Y)
SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM CTE2;

--== CROSS JOIN ==--
SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))b(N);

dwain.c (10/3/2012)

---

Jason-299789 (10/3/2012)

---

I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned.

OK. So I would have been 3.5 out of 4. What I don't get is the ISNULL around ROW_NUMBER().

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N
INTO yourTallyTable
FROM (VALUES(1))a(n);