June 28, 2008 at 2:01 pm
Jeff Moden (6/28/2008)
dfalso (6/27/2008)
In order to be truly safe, dynamic sql is needed, unless we could write a CTE with an input variable that governs the amount of recursion (again, on Monday maybe).Still, you haven't answered my question... How many times have you personally needed a Tally table larger than 11,000? And have you EVER heard of anyone needing over 121 million rows for a Tally table?
Well, I don't think anyone needs over 121M, but I wouldn't presume to know what people do with it. Personally, I work with 1M because I have need to analyze dates and such. But that's just me. And putting a check in shouldn't hurt anything and is not hard to write.
June 28, 2008 at 5:09 pm
Then, if you add a check, all this talk about dynamic SQL becomes a moot point. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2008 at 9:15 pm
Jeff Moden (6/27/2008)
Matt Miller (6/27/2008)
I personally roll with a tally table with a 1M limit, but that's a personal choice.Otherwise - it's very easy to come up with a numbers table of pretty much any size you need. CTE method coming up.
;with cte1 (N) as (select 1 union select 2), --2
cte2 as (select c1.N from cte1 c1, cte1 c2, cte1 c3, cte1 c4), --16
cte3 as (select c1.N from cte2 c1, cte2 c2, cte2 c3, cte2 c4), --65536
cte4 as (select c1.N from cte3 c1, cte3 c2, cte3 c3, cte3 c4) --1.84467E+19
select row_number() over (order by N) as Num
from CTE4
I like my method... it's about as fast and will easily make up to 121 million numbers... if you need more than that, just add 1 more cross-join... but, I submit... if you need more than a 121 million numbers, you're probably doing something wrong...
DECLARE @Top INTSET @Top = 100000
;WITH cteTally
AS (
SELECT TOP(@Top) ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N
FROM Master.sys.SysColumns sc1,
Master.sys.SysColumns sc2)
SELECT *
FROM cteTally
Oh I know. It's just that I can more easily control the number I need to generate (when you get that big of a set you really don't want to generate an extra several BILLION rows if you don't need them). I have had somewhat regular need for tally up to about 1/2 million, which was why I kept it at 1M. After that though - I'd have to think something is severely wrong if you need a tally much bigger than that.
Besides - I sometimes like to mix it up just a little so that I can remember why I prefer my tried and true methods....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 28, 2008 at 10:31 pm
Thanks Matt...
Hey... like I said, take the exact code you posted and try to gen a 1000 rows... you'll see why...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2008 at 10:42 pm
you mean the extra comma? ...hehe...:D
Let me go correct that
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 28, 2008 at 11:57 pm
That's the big thing... the other thing not so obvious is you don't show how to limit the output to any given number. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 9:51 am
You're right of course - here's an actual useable version. This would create a temporary numbers table (1M rows), with the requisite index to make it all worthwhile...
drop table #temp
;with cte1 (N) as (select 1 union select 2), --2
cte2 as (select c1.N from cte1 c1, cte1 c2, cte1 c3, cte1 c4), --16
cte3 as (select c1.N from cte2 c1, cte2 c2, cte2 c3, cte2 c4), --65536
cte4(n) as (select null from cte3 c1,cte2 c2,cte2 c3)
select top 1000000
row_number() over (order by N) as Num
into #temp
from CTE4
create unique clustered index pk_tmp on #temp(Num)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 29, 2008 at 10:42 am
Matt Miller (6/28/2008)
Oh I know. It's just that I can more easily control the number I need to generate (when you get that big of a set you really don't want to generate an extra several BILLION rows if you don't need them). I have had somewhat regular need for tally up to about 1/2 million, which was why I kept it at 1M....
Besides - I sometimes like to mix it up just a little so that I can remember why I prefer my tried and true methods....
Heh... ok... let's do like we always do... let's "mix it up just a little" for our mutual benefit and maybe for those that read our posts... 😀
The following contains 3 "programmable" Tally tables... test harness (you'll recognize the method) is the same for all 3... Ask yourself some questions... Which ones run faster on the first run? Which ones run faster on subsequent runs? Which ones are easier to remember for coding purposes? Which ones are more readable?
[font="Courier New"]DECLARE @Bitbucket INT
DECLARE @TestSize INT
SET @TestSize = 1000000
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100) ---------------------------------------------------------
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
PRINT '===== Matt Miller''s Method ====='
SET STATISTICS IO ON
SET STATISTICS TIME ON
;with cte1 (N) as (select 1 union select 2), --2
cte2 as (select c1.N from cte1 c1, cte1 c2, cte1 c3, cte1 c4), --16
cte3 as (select c1.N from cte2 c1, cte2 c2, cte2 c3, cte2 c4), --65536
cte4(n) as (select null from cte3 c1,cte2 c2,cte2 c3)
select top (@TestSize)
@Bitbucket = row_number() over (order by N)
from CTE4
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100) ---------------------------------------------------------
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
PRINT '===== Itzek''s Method ====='
SET STATISTICS IO ON
SET STATISTICS TIME ON
; WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY C) AS n FROM L5)
SELECT top (@TestSize) @Bitbucket = N FROM Nums
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100) ---------------------------------------------------------
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
PRINT '===== Jeff Moden''s Method'
SET STATISTICS IO ON
SET STATISTICS TIME ON
;WITH cTally AS
(-----------------------------------------------------------------------------
SELECT TOP (@TestSize)
ROW_NUMBER() OVER (ORDER BY t1.ID) AS N
FROM Master.sys.SysColumns t1
CROSS JOIN Master.sys.SysColumns t2
)-----------------------------------------------------------------------------
SELECT @Bitbucket = N FROM cTally
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 11:24 am
Matt Miller (6/28/2008)
It's just that I can more easily control the number I need to generate
Just messing with you a bit now, ol' friend... It's pretty easy to control any of the methods, but consider the speed and the easy read (I'm a poet and don't know it 😛 )...
[font="Courier New"]--===== Declare some local variables that could be parameters in a proc
DECLARE @StartNumber INT,
@EndNumber INT,
@Increment INT
--===== Set those "parameters" for demonstration purposes
-- If you try to "trick" it, CTE will error as expected.
SELECT @StartNumber = 1000, --Min if @Increment >1, Max if @Increment <1
@EndNumber = -100, --Max if @Increment >1, Min if @Increment <1
@Increment = -3 --Must be (-) When @EndNumber < @StartNumber
; WITH cteTally AS
(-----------------------------------------------------------------------------
--==== High performance CTE equivalent of a Tally or Numbers table with
-- range and increment controls. --Jeff Moden
SELECT TOP (((@EndNumber-@StartNumber)/@Increment)+1)
ROW_NUMBER() OVER (ORDER BY t1.ID)
* @Increment
+ (@StartNumber-@Increment) AS N
FROM Master.sys.SysColumns t1
CROSS JOIN Master.sys.SysColumns t2
)-----------------------------------------------------------------------------
SELECT N FROM cteTally [/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 12:16 pm
All right - no problem. Under those circumstances - mine is actually the slowest of the three methods, then Brother Itzik's, with the fastest being the cross join.
Why is mine the slowest (by far)? In short - one single word. By not using UNION ALL (even on such a trivial operation), mine tends to take twice as long as the other two. (I thought initially it was the multiple cross joins, but no. Add the work ALL to the initial UNION and mine ends back up in the same range as Itzik's).
As to the other two - both are in fact very good, tend to vast amount of numbers rather quickly. The cross join on a system is a little faster; on the other hand, a lot of shops have prohibitions about using system objects in this fashion, so Itzik's method might be worth remembering.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 29, 2008 at 12:47 pm
Nah... After adding the "ALL", yours is just as fast, fewer lines to remember, and easier on the eyes.
a lot of shops have prohibitions about using system objects in this fashion
True enough... If they don't allow the exception, that's when I break out the porkchops 😛 The rest of the world is fairly oblivious... they don't do code reviews and the DBA (if there is one) either doesn't care (reason for no code reviews) or isn't responsible for the promotion of code.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2008 at 1:05 pm
Any of this cow left for me? 🙂
Here is my entry (really just a tweak of Jeff's):
PRINT REPLICATE('=',100) ---------------------------------------------------------
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
declare @root int
select @root = sqrt(@testsize)+1
PRINT '===== RBarryYoung''s Method'
SET STATISTICS IO ON
SET STATISTICS TIME ON
;WITH cTally AS
(-----------------------------------------------------------------------------
SELECT TOP (@TestSize)
ROW_NUMBER() OVER (ORDER BY t1.ID) AS N
FROM (Select TOP (@root) ID from Master.sys.SysColumns) t1
CROSS JOIN (Select TOP (@root) ID from Master.sys.SysColumns) t2
)-----------------------------------------------------------------------------
SELECT @Bitbucket = N FROM cTally
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
The strange thing is that I am getting much different numbers than you guys...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 1:31 pm
So now, what's the minimum number of rows guaranteed to be in master's syscolumns?
June 29, 2008 at 4:44 pm
On 2005, it's always been 11,000+ wherever I've looked.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 5:08 pm
rbarryyoung (6/29/2008)
Any of this cow left for me? 🙂Here is my entry (really just a tweak of Jeff's):
Nicely done, Barry... but I get numbers just a bit slower (not much... about 20 ms on a million rows) because of the control you've put on the cross join... faster to let nature take its course.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 271 through 285 (of 309 total)
You must be logged in to reply to this topic. Login to reply