Generating Sequential numbers between n1 and n2
-
Hello,
I have a requirement in SQL SERVER 2000 (not 2005) wherein I need a function that will return all the numbers between two given numbers, n1 and n2. For example, when n1=1 and n2= 100, I need to get all the numbers between 1 and 100..
What is the FASTEST way to get the result?. I do not want to take the cursor approach, since that will be slower when n1 = 1 and n2= 100,000..
Can someone provide me with any ideas?.
Thanks,
Ganesh
-
Use the F_TABLE_NUMBER_RANGE function on this link:
-
Thank you.. This is great.
One question though.. I cannot seem to get it working when the numbers are really large. For example,
select count(*) from dbo.F_TABLE_NUMBER_RANGE(1,999999999)
returns zero rows because of the check at the bottom that reads
...when abs(@START_NUMBER-@END_NUMBER)+1 <= 16777216
Any option available for me now to generate between 1 and 999999999?
Thanks,
Ganesh
-
Yu need to put the square root of the number of rows needed, ( convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1))) ), into a temp table using the F_TABLE_NUMBER_RANGE function, and then cross join the temp table against itself, just like the function does.
For example, for 1 through 999999999, you will need to have 31623 rows in the temp table, 0 through 31622. When you cross them, you must multiply the numbers from the second half of the cross by convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1))). This is basically what the F_TABLE_NUMBER_RANGE function does internally.
I will let you figure out the details and test it.
However, I think you will find that generating 1 billion rows will take a very long time, maybe several hours or more, and the output table would be 4 GB in size. Why do you need to do that?
-
--This would take about 2 hours to run on my pc.
SELECT TOP 999999999
IDENTITY(int, 1,1) AS Demo
INTO #Test
FROM master.dbo.SysColumns C1
CROSS JOIN master.dbo.SysColumns C2
CROSS JOIN master.dbo.SysColumns C3
SELECT COUNT(*) AS Total, MIN(Demo) AS Minimum, MAX(Demo) AS Maximum FROM #Test
DROP TABLE #Test
-
Ganesh,
There's only a couple of reasons why I could ever see for generating the numbers from 1 to a billion... and none of them are good. For example... lot's of folks think they need to do such a thing to find missing ID's in an IDENTITY column... finding missing ID's is OK, but not if you intend to reuse them... reusing ID's goes against the very nature of why you have a unique column to begin with.
If you really, really need to do that because someone will shoot you in the head if you don't or you'll loose your job, then, there are better ways to do it (although I'd seriously consider finding a new job if asked to do such an insane thing)... for example... find all missing ID's...
--===== This short little ditty is what does the actual work
SELECT MissingFrom = (SELECT ISNULL(MAX(suba.CustID),0)+1
FROM Customer suba WITH (NOLOCK)
WHERE suba.CustID < a.CustID),
MissingTo = a.CustID - 1
FROM Customer a WITH (NOLOCK)
WHERE a.CustID - 1 NOT IN (SELECT CustID FROM Customer WITH (NOLOCK))
AND a.CustID - 1 > 0
...and it finds all the ranges and singleton rows of ALL missing ID's on a very wide 1.5 million row table in just seconds.
Now, with that in mind, would you PLEASE tell us what you're trying to accomplish so we can help you find a better way? The real task... not the task of generating numbers...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Remi...
Just sharing info...
57 Minutes on my 1.8 Ghz, 2GB pc at home running Developer's Edition of 2000 at sp3a.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
All,
Thanks for your help.. The requirement for generating this numbers is not to be used for any database operations. Another application requires data (in a file) in this format:
AB1
AB2
AB3
AB4
AB5
AB51
AB52
AB53
AB6
AB61
AB66
AB67
AB68
AB69
AB70
TR1
TR2
TR3
....
....
TEF1230
TEF1231
TEF1232
TEF1237
....
..
As you may see , it requires a sequential set of numbers barring some of the numbers you do not see (for example, AB62 to AB65 is not needed) that I later delete after the sequential numbers are generated.
I know what are the ones I do NOT want (AB62 to AB65) and I do know what the start and end of the numbers.
The numbers here 62, 63 are just examples and in the actual requirement it may go up to 9 digits..
This is the requirement.. What I do is get the unique "AB" or "TR" or whatever alpha text is and then send the start and end numbers for each one of them to the number_range function, get all the sequential numbers and then delete the ones I do not want..
--Ganesh
-
What's I see for the moment would be to bite the bullet and let it run for an hour while it inserts to a table once. Then I'd build a stored proc that can take in exception parameters (ranges you do not want). Then have the server bcp the data to text file. This is assuming that this is not a one off request. But even if it is I would still build it so it could be reused some day.
-
What I see is that you shouldn't worry about having each letter prefix start with 1. There should be an IDENTITY column and a prefix column. Concatenate the two when you need to make a return with both. Then, you don't have to maintain a separate table with a billion rows in it and everything will be automatic. If you really need to start each prefix at 1 (should NOT be a requirement), then make 3 tables with identity columns and don't combine the prefixes with the numbers until you need to return from your app.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
On second thought, Ganesh, I see where you say "The requirement for generating this numbers is not to be used for any database operations"... why even go near the database for that? Why not just have the application do it, then? Nice tight FOR/NEXT or DO/WHILE loop should do it and it'll be nasty fast...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
My desktop server ran it in 43 minutes. It's a Dual 3.0 Gh Xeon with 1.5 Gb of RAM.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply