quote:

---

Originally posted by Xerxes

quote:

---

Originally posted by Michael Valentine Jones

I found it more useful to have a table valued function to return the numbers you need exactly.

The one I wrote returns anything up to 10,000 rows in less than a second, and will return 1,000,000 rows in about 10 seconds, so the performance hit is not huge in most situations. It slows down above that, so I limited the results to 10,000,000 rows, which it takes about two minutes to generate. This seems to cover the vast majority of situations.

Of course, you could store a useful number of rows in a permanent table if you need to use it a lot and are concerned with performance. You can still have a function around for when you find that more useful.

-- Return numbers 1 to 2000
select NUMBER from dbo.UDF_NUMBER_RANGE(1,2000)

-- Return numbers -14 to 3147
select NUMBER from dbo.UDF_NUMBER_RANGE(-14,3147)

Codo Ergo Sum

---

Michael,

You mean you're suggesting I do this for a permanent numbers table instead of just the old 1 - 8000 that the SQL Warrior Queen suggested?

Semper fi,

Xerxes, USMC(Ret.)
------------------------------------------------------
Once a Marine ALWAYS a Marine!

---

CREATE FUNCTION dbo.getSequence
(
	@BeginNum int,
	@EndNum int,
	@Incr int
)
RETURNS @Sequence Table
(
	n int
)
AS
BEGIN 
	While @BeginNum <= @EndNum
	Begin
		Insert Into @Sequence (n) 
		Select n = @BeginNum

		Select @BeginNum = @BeginNum + @Incr
	End
	RETURN
END

quote:

---

Originally posted by tduggan

The permanent numbers table is the way to go for performance. You should have a clustered index on the n column in that table. It will be much faster than a UDF that builds it each time.

Tara

---

quote:

---

But where I work, the DBA wins as that is what we are here to do (partially of course as I have other things that I do).

---

quote:

---

Originally posted by tduggan

Well where I work, our applications (each major version of it) go through about 3 months of performance testing. When your queries typically run in about 500 milliseconds, you want to squeeze out a few milliseconds. And that's with a 10GB database. Now if you've got a different scenario where queries are taking a few seconds to run, then a few milliseconds isn't going to be noticed as the user already has to wait. I personally see a few milliseconds worth it regardless though. But you guys might not have the strict performance requirements that we do here, so you might not be as concerned with it.

The performance issue is the time that it takes to generate the numbers. Not with the data size or anything else. And yes if you are constantly having to generate these, then why not have a static table? Really, why not? I personally don't see ease of coding as an answer. I strive to write good code and not just code that is easy. Typically people who do this type of thing will use dynamic SQL, cursors, select *, and all sorts of other bad T-SQL programming practices. I'm not trying to hit anyone in this thread about this, I'm just trying to give you the point of view of a DBA. Often times, the DBA is at odds with the developer when it comes to these sorts of things. But where I work, the DBA wins as that is what we are here to do (partially of course as I have other things that I do).

Tara

---

quote:

---

Originally posted by rockmoose

Nothing says that one can't use both techniques.
If there is code that makes use of a number table and is frequently run,
then it just makes no sense to NOT use a static table for performance issues.
For one of situations, saving a few milliseconds is not as important, and you might use what you feel comfy with.

I have usually have both options at hand, and use what feels best for the task at hand.

rockmoose

---

quote:

---

Originally posted by spirit1

arghhhhh ..... i was hoping you'd come up with something that has no loop.
i was playing with it and can't find a way to do that with no loop...

Go with the flow & have fun! Else fight the flow

---

Declare @beginN int,
	@EndN int,
	@Step int

Select 
	@BeginN = -14,
	@EndN = 53,
	@step = 3

Declare @s table (n int)

Insert Into @s
Select n = @beginN+(n1+n2+n3+n4+n5+n6+n7+n8+n9+n10+n11+n12+n13+n14)*@step
From (Select * From (Select n1=0 Union All Select power(2,0)) A Where n1<=abs((@BeginN - @EndN)/@step)) n1
Cross Join (Select * From (Select n2=0 Union All Select power(2,1)) A Where n2<=abs((@BeginN - @EndN)/@step)) n2
Cross Join (Select * From (Select n3=0 Union All Select power(2,2)) A Where n3<=abs((@BeginN - @EndN)/@step)) n3
Cross Join (Select * From (Select n4=0 Union All Select power(2,3)) A Where n4<=abs((@BeginN - @EndN)/@step)) n4
Cross Join (Select * From (Select n5=0 Union All Select power(2,4)) A Where n5<=abs((@BeginN - @EndN)/@step)) n5
Cross Join (Select * From (Select n6=0 Union All Select power(2,5)) A Where n6<=abs((@BeginN - @EndN)/@step)) n6
Cross Join (Select * From (Select n7=0 Union All Select power(2,6)) A Where n7<=abs((@BeginN - @EndN)/@step)) n7
Cross Join (Select * From (Select n8=0 Union All Select power(2,7)) A Where n8<=abs((@BeginN - @EndN)/@step)) n8
Cross Join (Select * From (Select n9=0 Union All Select power(2,8)) A Where n9<=abs((@BeginN - @EndN)/@step)) n9
Cross Join (Select * From (Select n10=0 Union All Select power(2,9)) A Where n10<=abs((@BeginN - @EndN)/@step)) n10
Cross Join (Select * From (Select n11=0 Union All Select power(2,10)) A Where n11<=abs((@BeginN - @EndN)/@step)) n11
Cross Join (Select * From (Select n12=0 Union All Select power(2,11)) A Where n12<=abs((@BeginN - @EndN)/@step)) n12
Cross Join (Select * From (Select n13=0 Union All Select power(2,12)) A Where n13<=abs((@BeginN - @EndN)/@step)) n13
Cross Join (Select * From (Select n14=0 Union All Select power(2,13)) A Where n14<=abs((@BeginN - @EndN)/@step)) n14
Where @beginN+(n1+n2+n3+n4+n5+n6+n7)*@step < @EndN
Order By n

Select * from @s