Create a seqence of numbers

[Michael C]

Hi People,

I would like to have an sql statement that would return me a simple sequence
of numbers, something like this:

SELECT * FROM GenerateSequence(1, 4) would simply return

1
2
3
4

I need this to be part of a user defined function so a loop, temp table etc
is no good. I thought maybe there was something new in sql2005 that was
built in for this but couldn't find anything. I need this for various
reasons that have cropped up over the years. I've implemented something
using a recursive CTE but it's not perfect as it errors at a certain limit
and is slow.

Thanks for any replies,
Michael

[Adi]

You can create a numbers table and select from it or use a CTE just
like you did. The CTE has limitations on number of recursion, but you
can use the maxrecursion hint to change or even cancel the limitation
(not that it is recommended that you’ll call the recursion so many
times).

Adi

[Plamen Ratchev]

Here is one method that will be very efficient:

CREATE FUNCTION GenerateSequence (@start INT, @end INT)
RETURNS TABLE
AS
RETURN
WITH
Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),
Nums (n) AS
(SELECT ROW_NUMBER() OVER(ORDER BY n)
FROM Num6)
SELECT n FROM Nums
WHERE n BETWEEN @start AND @end;

GO

SELECT n FROM GenerateSequence(1, 4);

--
Plamen Ratchev
http://www.SQLStudio.com

[Michael C]

"Adi" <adi...@hotmail.com> wrote in message news:01327ecd-d08f-4028-8d20-

> You can create a numbers table and select from it or use a CTE just
> like you did. The CTE has limitations on number of recursion, but you
> can use the maxrecursion hint to change or even cancel the limitation
> (not that it is recommended that you’ll call the recursion so many
> times).

This seems to work pretty well. I thought it had problems but it looks like
the server was just bogged down doing other work. Any comments? The Level
column is only really shown for debug purposes.

Cheers,
Michael

usage:
SELECT * FROM dbo.udfSequence(1000)

ALTER FUNCTION [dbo].[udfSequence]
(@MaxValue INT)
RETURNS TABLE
AS

RETURN
WITH SEQ_INNER AS
(
SELECT 1 AS [Value]
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
),
CTE AS
(
SELECT [Value], 1 AS LEVEL FROM SEQ_INNER WHERE [Value] <= @MaxValue
UNION ALL
SELECT CTE.[Value] * 10 + SEQ_INNER.VALUE, LEVEL + 1
FROM CTE
CROSS JOIN SEQ_INNER
WHERE CTE.[Value] * 10 + SEQ_INNER.VALUE <= @MaxValue
)
SELECT TOP 2147483647 [Value], LEVEL FROM CTE ORDER BY [Value]

[Michael C]

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:1sCdnZfxzrf_9qrU...@speakeasy.net...

Thanks, I have emailed this to myself and will look at it on monday as I'm
just leaving for the weekend now.

Cheers,
Michael

[Michael C]

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:1sCdnZfxzrf_9qrU...@speakeasy.net...

> Here is one method that will be very efficient:

Hey that works really well, it's faster and simpler than my routine and as
you said very efficient. The question I have is why? It appears to create 4
billion rows even if you only filter out 4.

Michael

[--CELKO--]

>> I need this to be part of a user defined function so a loop, temp table etc <<

NO! you think like a procedural programmers, not an SQL programmer.
SQL does best with tables and joins.

[Uri Dimant]

Michael
create table t(i int not null identity primary key)

go 1000

select * from t where...

"Michael C" <mi...@nospam.com> wrote in message
news:OIGh3dcV...@TK2MSFTNGP03.phx.gbl...

[Uri Dimant]

Sorry, should be INSERT statement

INSERT INTO tbl DEAFULT VALUES
GO 1000

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:%23xacgpf...@TK2MSFTNGP02.phx.gbl...

[Plamen Ratchev]

It is efficient because the query will not generate 4 billion rows if
you filter 4 rows. It will generate only 4 rows. Try running with
different range size and you will see the time to complete will be
different. SQL Server stops calculating row numbers beyond the filter
restrictions.

[--CELKO--]

There is a thread with lots of different methods over at IT Toolbox
website.

[TheSQLGuru]

If possible you should make this a permanent table with clustered 100% fill
factor index on then number then reference that. There are MANY uses for
such a table. Search simple-talk.com for a dissertation.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"Michael C" <mi...@nospam.com> wrote in message

news:er2WwTeV...@TK2MSFTNGP06.phx.gbl...

[Rich Dillon]

Good function. It's only quick though when @start = 1 (or a small number).

SET STATISTICS TIME ON;

SELECT n FROM GenerateSequence(1, 4);

SELECT n FROM GenerateSequence(100000001,100000004);

Here's a minor modification:

CREATE FUNCTION dbo.IntSequence (@start INT, @end INT)
RETURNS TABLE AS

RETURN
WITH
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS x, N1 AS y),
N3 (n) AS (SELECT 1 FROM N2 AS x, N2 AS y),
N4 (n) AS (SELECT 1 FROM N3 AS x, N3 AS y),
N5 (n) AS (SELECT 1 FROM N4 AS x, N4 AS y),
N6 (n) AS (SELECT 1 FROM N5 AS x, N5 AS y),
Nx (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM N6)
SELECT @start - 1 + n AS n
FROM Nx
WHERE n <= @end - @start + 1;

GO

SET STATISTICS TIME ON;
SELECT n FROM dbo.IntSequence(1, 4);
SELECT n FROM dbo.IntSequence(100000001,100000004);

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message

news:vM2dnfpcC5hYTKrU...@speakeasy.net...

[Plamen Ratchev]

Good point. I find normally you do not need a range, but rather sequence
starting from 1 and only an upper limit. Your addition helps to maintain
the row number generation from 1 to upper limit, which keeps performance
optimal.