jw.lankhaar (6/13/2013)
For a limited number of rows (< 100) using a recursive common table expression is an alternative:
DECLARE @lowerbound int
DECLARE @increment int
DECLARE @upperbound int
SET @lowerbound = -5
SET @upperbound = 90
SET @increment = 3
;
WITH seq AS (
SELECT @lowerbound AS n
UNION ALL
SELECT seq.n + @increment AS n
FROM seq INNER JOIN (SELECT NULL AS a) a ON
n < @upperbound
)
SELECT n FROM seq
Note that due to recursion, the number of returned rows may not exceed 99.
Jan-Willem Lankhaar
DECLARE
@lowerbound INT,
@increment INT,
@upperbound INT
SELECT
@lowerbound = -5,
@upperbound = 2800000,
@increment = 3;
SELECT x.Start + y.Inc
FROM [dbo].[InlineTally] (1000000) t
CROSS APPLY (SELECT Start = (t.n+@lowerbound-1)) x
CROSS APPLY (SELECT Inc = (t.n-1)*(2)) y
WHERE x.Start + y.Inc <= @upperbound;
-- (933,336 row(s) affected) / 00:00:05
WITH seq AS (
SELECT n = @lowerbound
UNION ALL
SELECT seq.n + @increment
FROM seq
WHERE n < @upperbound
)
SELECT n FROM seq OPTION(MAXRECURSION 0);
-- (933,336 row(s) affected) / 00:00:12
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden