September 22, 2009 at 11:14 am
Great article Lynn. It seems that even if you aren't permitted to change the schema that if you are going to use it multiple times you might get better results by putting it into a temp table instead of generating it dynamically over and over.
Still, if it must be dynamically generated, this is a great technique.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 22, 2009 at 11:25 am
timothyawiseman (9/22/2009)
Great article Lynn. It seems that even if you aren't permitted to change the schema that if you are going to use it multiple times you might get better results by putting it into a temp table instead of generating it dynamically over and over.Still, if it must be dynamically generated, this is a great technique.
True enough. But I've found that the same sites that don't allow a permanent Tally table also have an "angry" DBA the will frequently not allow any TempDB usage nor any access to things like sysColumns in any code... that's where dynamic generation can really save the day especially if it's nasty fast like this one is.
Heh... here's to "angry" DBA's... I get to charge for the time I take to work around their restrictions. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2009 at 12:48 pm
Nice article but it has a problem: the function CAN'T be created (or it's code used) on SQL server 2000 because of the CTE approach and and row_number() function; beside that I find the code logic a bit confusing even for experimented sqlists due to its relative complexity.
Here is another version that WILL work on any SQL version and the code is pretty much self descriptive and easy to understand by everyone. I agree that might not have ALL the features the code in article has but almost all. Still has the start/end/increment values and can be sorted asc desc depending of the needs but the one from my example is limited to 1,000,000 numbers (more than enough vast majority of cases) but it can be easily extended in the code. One little drawback is that you have to order this version of function I built when used:
if object_id('[dbo].[ufn_Tally_zb]') is not null
drop function [dbo].[ufn_Tally_zb]
GO
CREATE function [dbo].[ufn_Tally_zb](
@pStartValue int,
@pEndValue int,
@pIncrement int
)
returns table
as
return
(
select
number
from
(
select
hth.dg*100000+
tth.dg*10000+
th.dg*1000+
h.dg*100+
t.dg*10+
u.dg+1 as number
from
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as u
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as t
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as h
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as th
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as tth
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as hth
) nr
where
number between @pStartValue and @pEndValue
and number % @pIncrement = 0
)
GO
-- to execute
select number from [dbo].[ufn_Tally_zb] (1,100000,1) order by number
I agree though that this is more like an intellectual exercise rather than a needed feature. Is better to use a tally table already created but hey, just in case we need it on the fly.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
September 22, 2009 at 12:51 pm
Jeff,
First, leave it to you squeeze another 33% out of a routine, awesome. I'll have to incorporate that change into my code. I think Steve needs to add a disclaimer to the top and bottom of this articel:
As good as this article is, you really need to read the discussion that follows. It is very informative and enlightening.
Second, I actually had to take a walk after reading your post. I was extremely humbled, but at the same time flying high like a school kid who just aced the toughest test. Between your praise and being mentioned in the same paragraph as as Itzek, it was just too much. Now I have bring myself down to earth and start working on my next article, one that I hope to submit to SQL Server Standard. Now the bar is set, so I know I really have to make sure I get all my ducks in a row.
Thank you for being the mentor you are, I don't think I'd be where I am today if it hadn't been for you and SSC. Not knocking the others like Gail, Grant, Gus, Steve, Barry, Jack, Wayne, David, et al., as they too have been great in teaching me new things as well.
September 22, 2009 at 1:12 pm
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:
declare @i bigint
set @i = 0
select @i=@i+1 as num from largest_table
September 22, 2009 at 1:17 pm
laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:declare @i bigint
set @i = 0
select @i=@i+1 as num from largest_table
That's nice, but let's see you use that in a join or even as a stand-alone result set.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2009 at 1:18 pm
laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:declare @i bigint
set @i = 0
select @i=@i+1 as num from largest_table
Not necessarily true, it really depends on what you are using the Tally table for how big a Tally table you really need.
September 22, 2009 at 1:26 pm
the sqlist (9/22/2009)
Nice article but it has a problem: the function CAN'T be created (or it's code used) on SQL server 2000 because of the CTE approach and and row_number() function; beside that I find the code logic a bit confusing even for experimented sqlists due to its relative complexity.Here is another version that WILL work on any SQL version and the code is pretty much self descriptive and easy to understand by everyone. I agree that might not have ALL the features the code in article has but almost all. Still has the start/end/increment values and can be sorted asc desc depending of the needs but the one from my example is limited to 1,000,000 numbers (more than enough vast majority of cases) but it can be easily extended in the code. One little drawback is that you have to order this version of function I built when used:
if object_id('[dbo].[ufn_Tally_zb]') is not null
drop function [dbo].[ufn_Tally_zb]
GO
CREATE function [dbo].[ufn_Tally_zb](
@pStartValue int,
@pEndValue int,
@pIncrement int
)
returns table
as
return
(
select
number
from
(
select
hth.dg*100000+
tth.dg*10000+
th.dg*1000+
h.dg*100+
t.dg*10+
u.dg+1 as number
from
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as u
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as t
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as h
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as th
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as tth
cross join
( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as hth
) nr
where
number between @pStartValue and @pEndValue
and number % @pIncrement = 0
)
GO
-- to execute
select number from [dbo].[ufn_Tally_zb] (1,100000,1) order by number
I agree though that this is more like an intellectual exercise rather than a needed feature. Is better to use a tally table already created but hey, just in case we need it on the fly.
Heh... the logic is simpler but it's still an eyefull, isn't it? 🙂
The other problem with that method is performance. It takes more than a minute to gen 10Meg rows (after the addition of another cross join) and it takes over a second to generate just 1000 rows. As you said, rather than use such a method on 2k, I'd recommend just using a permanent Tally table... its not worth the cost of having programmable on-the-fly Tally tables in 2k.
Still, there are some very fast ways to generate on-the-fly tables in 2k...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2009 at 1:28 pm
Lynn Pettis (9/22/2009)
laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:declare @i bigint
set @i = 0
select @i=@i+1 as num from largest_table
Not necessarily true, it really depends on what you are using the Tally table for how big a Tally table you really need.
Heh... better than that, Lynn... let's see someone use it for something practical and set based. You just can't do a join to the code above.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2009 at 1:32 pm
laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:declare @i bigint
set @i = 0
select @i=@i+1 as num from largest_table
What happened to the good old count:
declare @i bigint
select @i=count(*) from largest_table
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
September 22, 2009 at 1:38 pm
Jeff Moden (9/22/2009)
Heh... the logic is simpler but it's still an eyefull, isn't it? 🙂
The other problem with that method is performance. It takes more than a minute to gen 10Meg rows (after the addition of another cross join) and it takes over a second to generate just 1000 rows. As you said, rather than use such a method on 2k, I'd recommend just using a permanent Tally table... its not worth the cost of having programmable on-the-fly Tally tables in 2k.
Still, there are some very fast ways to generate on-the-fly tables in 2k...
Jeff, I don't know what test you performed because I get totally different numbers. My function has same perfromance results like the one from article. The one from article just took 1:36 min to generate the 10Meg of numbers, same as mine(based on your results). Mine takes 1 sec to generate 100k and not 1k of numbers.
Try harder. 🙂
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
September 22, 2009 at 1:43 pm
the sqlist (9/22/2009)
Jeff Moden (9/22/2009)
Heh... the logic is simpler but it's still an eyefull, isn't it? 🙂
The other problem with that method is performance. It takes more than a minute to gen 10Meg rows (after the addition of another cross join) and it takes over a second to generate just 1000 rows. As you said, rather than use such a method on 2k, I'd recommend just using a permanent Tally table... its not worth the cost of having programmable on-the-fly Tally tables in 2k.
Still, there are some very fast ways to generate on-the-fly tables in 2k...
Jeff, I don't know what test you performed because I get totally different numbers. My function has same perfromance results like the one from article. The one from article just took 1:36 min to generate the 10Meg of numbers, same as mine(based on your results). Mine takes 1 sec to generate 100k and not 1k of numbers.
Try harder. 🙂
I'm using SQL SERVER Profiler with the data produced by the queries routed to a temp table. Routing the output to the screen gives false readings because it takes so long to display the data that it looks like all methods are the same. That's why I called outputting to the screen the "great equalizer".
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2009 at 1:45 pm
the sqlist (9/22/2009)
laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:declare @i bigint
set @i = 0
select @i=@i+1 as num from largest_table
What happened to the good old count:
declare @i bigint
select @i=count(*) from largest_table
Again, that's fine.... but what would you do with it? It doesn't produce a list of numbers that can be joined to nor can it be used to spawn things like dates. Instead, it produces just 1 number.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2009 at 1:47 pm
Jeff Moden (9/22/2009)
Lynn Pettis (9/22/2009)
laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:declare @i bigint
set @i = 0
select @i=@i+1 as num from largest_table
Not necessarily true, it really depends on what you are using the Tally table for how big a Tally table you really need.
Heh... better than that, Lynn... let's see someone use it for something practical and set based. You just can't do a join to the code above.
I guess I should have editted the quote to just the first line. Even with the the phrase "One usually ...", just not very accurate. What if your largest table is only a couple of hundred rows long but you have to parse 2000 character strings on a regular basis?
September 22, 2009 at 1:56 pm
Jeff Moden (9/22/2009)
I'm using SQL SERVER Profiler with the data produced by the queries routed to a temp table. Routing the output to the screen gives false readings because it takes so long to display the data that it looks like all methods are the same. That's why I called outputting to the screen the "great equalizer".
I guess you're right, here is a much simpler way to test performance though:
set statistics time on
select number into #tmp from [dbo].[ufn_Tally_zb] (1,100000,1) order by number
drop table #tmp
The CPU time in ms is given in the Message tab. (i forgot to mention this initially)
Of course, you have to execute both functions using the example template above in different windows and not at the same time. Mine it is much slower indeed becasue creates from start all 1Meg of rows and filters them later. For big numbers though the difference drops drastically.
However, in order to make the function run on SQL 2000 and earlier you have to use my approach. Actually performance wasn't really my goal here and I asume I could tweak it a little bit. Not today though. 😛
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
Viewing 15 posts - 31 through 45 (of 159 total)
You must be logged in to reply to this topic. Login to reply