How to return a range of sequence number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to return a range of sequence number

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2005-12-29 : 02:26:56

Dear All,
I would like to use a query to return a range of sequence. That is, a range of, for example, 10000 to 19999. The starting and ending range is configurable. Moreover, it would be favourable to just select something without insert any thing to a temp table.

Can this be done by using SQL?

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 03:37:03

Have a number table and do query there


Create table Numbers(num int)
declare @i int
set @i=1

While @i<=20000
Begin
	insert into Numbers values(@i)
	set @i=@i+1
End

Select num from Numbers where num between 10000 and 19999

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 04:00:42

>>it would be favourable to just select something without insert any thing to a temp table.

Dont edit the question adding new constraints after it gets reply

Can you give more details on where you want to show these numbers?

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-29 : 08:19:06

This sounds exactly like the function that MVJ wrote. (If I never said this before Michael, I love this function)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-29 : 09:37:01

quote:
Originally posted by TG

This sounds exactly like the function that MVJ wrote. (If I never said this before Michael, I love this function)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Be One with the Optimizer
TG

Thanks! I was going to suggest it, but you beat me to it.

Do you find many uses for it? I find myself using it a lot to generate test data, especially in conjunction with NEWID() to generate random data.

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-29 : 09:55:10

>>Do you find many uses for it?

Like you, I use it to generate test data or as a "proof of concept" aid. It is not deployed on any of our production servers but then again neither is any kind of numbers table. We just don't tend to need that kind of thing in our production code. I like it because considering what it's achieving, it's remarkabley fast. For ranges less than about 64 I usually end up just manually punching out the "select 0 n1 union select 1..." thing. I should just get your function out on all our dev servers then I wouldn't need to do that :)

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-29 : 10:31:58

We have a small utility database with the same name on all development and production servers where we put utility objects like this. It's a little bit of work to setup the first time, but after that, it's just there. That way we always have the same "toolkit" available, and the code to reference objects like this is always the same:

select NUMBER from DBUTIL.dbo.F_TABLE_NUMBER_RANGE(1,30000)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources