InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:02:44 GMT20http://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxNice article...Using tally( or numbers table) is best way..Easy to code :).Tue, 15 May 2012 09:13:49 GMTGullimeelhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]David Burrows (8/19/2011)[/b][hr]Another great article Jeff :-)[quote][b]Jeff Moden (8/8/2011)[/b][hr]... some would say that I, indeed, have no taste buds[/quote]Surely you meant 'no taste' :-P[/quote]heh... that, too! And don't call me "Shirley". :-PFri, 19 Aug 2011 14:07:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxAnother great article Jeff :-)[quote][b]Jeff Moden (8/8/2011)[/b][hr]... some would say that I, indeed, have no taste buds[/quote]Surely you meant 'no taste' :-PFri, 19 Aug 2011 05:01:10 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Kenney Hill (8/11/2011)[/b][hr]Plus you could always let slip something embarrassing or funny or both about Jeff in the article!:hehe:[/quote]Heh... the fact that I talk with the dust bunnies isn't bad enough? :-PFri, 12 Aug 2011 06:20:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Bill Kline-270970 (8/11/2011)[/b][hr]Excellent article. A great example of why I come to SQLServerCentral.com . . . to learn from the best.[/quote]Thanks, Bill. :blush: I really appreciate the compliment but, gosh no... I'm not in "the best" category. There are plenty of folks that are much better at this than I. I just try really hard to keep up with all of them. :-)Fri, 12 Aug 2011 06:18:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Jeff Moden (8/10/2011)[/b][hr][quote][b]Paul Herbert (8/10/2011)[/b][hr]As usual a good article Moden. A minor correction if you don't mind. If you are going to quote my 'Really?!", I think it needs both a question mark & an exclamation point. That way it reads like I say it ... a touch of amusement & a ton of WTF. :)[/quote]Everyone... please meet and greet Mr. Paul Herbert... the Systems DBA where I work at. We've slain many dragons together in the short year or so that we've had the opportunity to work with each other. :-) I've been trying to convince him to write an article about some of the things he's done on the Administrative side of the world. Maybe you good folks can help me convince him. ;-)[/quote]Welcome Paul!I'd really be interested in an administrative article by you. The administrative side is my primary SQL job focus, but I know there are so many things that I could set-up better on that side.Plus you could always let slip something embarrassing or funny or both about Jeff in the article!:hehe:Thu, 11 Aug 2011 22:37:48 GMTKenney Hillhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxExcellent article. A great example of why I come to SQLServerCentral.com . . . to learn from the best.Thu, 11 Aug 2011 13:58:24 GMTBill Kline-270970http://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxNo, I use custom stored function for that purpose.-LeonardWed, 10 Aug 2011 18:34:18 GMTlstrashnoyhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Jon Russell (8/10/2011)[/b][hr]As usual, excellent! Thanks, Jeff.[/quote]You bet, Jon. Thanks for stopping by and for the feedback.Wed, 10 Aug 2011 18:31:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]lstrashnoy (8/10/2011)[/b][hr]Thanks Jeff.I actually agree. I used this code in mySQL and it works pretty efficient. Unfortunately SQL Server not optimizing the way mySQL does. And because it has to do sorting as the last step it kills performance :( In my mySQL environment I have numbers in millions and it does everything in memory without reads :)There is no analytical functions yet in mySQL anyways...Thanks,-Leonard[/quote]IIRC, doesn't mySQL have a builtin function that will generate numerical sequences?Wed, 10 Aug 2011 18:30:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Paul Herbert (8/10/2011)[/b][hr]As usual a good article Moden. A minor correction if you don't mind. If you are going to quote my 'Really?!", I think it needs both a question mark & an exclamation point. That way it reads like I say it ... a touch of amusement & a ton of WTF. :)[/quote]Everyone... please meet and greet Mr. Paul Herbert... the Systems DBA where I work at. We've slain many dragons together in the short year or so that we've had the opportunity to work with each other. :-) I've been trying to convince him to write an article about some of the things he's done on the Administrative side of the world. Maybe you good folks can help me convince him. ;-)Wed, 10 Aug 2011 18:28:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxAs usual a good article Moden. A minor correction if you don't mind. If you are going to quote my 'Really?!", I think it needs both a question mark & an exclamation point. That way it reads like I say it ... a touch of amusement & a ton of WTF. :)Wed, 10 Aug 2011 18:04:55 GMTPaul Herberthttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxAs usual, excellent! Thanks, Jeff.Wed, 10 Aug 2011 12:08:47 GMTJon Russellhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxThanks Jeff.I actually agree. I used this code in mySQL and it works pretty efficient. Unfortunately SQL Server not optimizing the way mySQL does. And because it has to do sorting as the last step it kills performance :( In my mySQL environment I have numbers in millions and it does everything in memory without reads :)There is no analytical functions yet in mySQL anyways...Thanks,-LeonardWed, 10 Aug 2011 11:11:16 GMTlstrashnoyhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Kenney Hill (8/10/2011)[/b][hr]Excellent article, as always, Jeff! And as all of your articles that I mange to see and read, this one has been added to my briefcase. I only wish I had the opportunity to work with you for even a few months, my thought processes and SQL code would be so much better for it!Thank you for the time and effort you put into these types of things (articles, forums, etc.).[/quote]Gosh, Kenney... that's one heck of a nice compliment. Thank you, Sir.Wed, 10 Aug 2011 11:09:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]John Dempsey (8/10/2011)[/b][hr][quote][b]Kenneth Wymore (8/10/2011)[/b][hr]Jeff,Where would I vote for you on the PASS site or is that already gone and past?Kenny[/quote]For those interested, Jeff is currently nominated for the award of Exceptional DBA and here is the link to vote: [url=http://www.exceptionaldba.com/vote/]RedGate Exceptional DBA voting link[/url][/quote]Thanks John!Wed, 10 Aug 2011 09:25:00 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Kenneth Wymore (8/10/2011)[/b][hr]Jeff,Where would I vote for you on the PASS site or is that already gone and past?Kenny[/quote]For those interested, Jeff is currently nominated for the award of Exceptional DBA and here is the link to vote: [url=http://www.exceptionaldba.com/vote/]RedGate Exceptional DBA voting link[/url]Wed, 10 Aug 2011 09:15:30 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxJeff,Where would I vote for you on the PASS site or is that already gone and past?KennyWed, 10 Aug 2011 08:49:30 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Jeff Moden (8/9/2011)[/b][hr][quote][b]ChrisM@Work (8/9/2011)[/b][hr]Crackin' good read as always Jeff.[/quote]Thanks, Chris. Always good to hear from you.I never noticed it before. Your signature line says you're a defender of moggies. We have 7 that we rescued from the streets. At one time, we had 15 of them, 4 dogs, and a rabbit and that was a little much. Even the dust bunnies got uncomfortable.Nah... didn't get rid of any of them. They all died of old age. I think we've given good lives to something like 40 or 50 of them.[/quote]Father Christmas and the petting farm :) I've rescued one or two in my time. Seven places yourself and your missus somewhere between charitable and saintly!On first reading your article I was a little concerned that folks might write off rCTE's altogether, despite your very clear wording. However, it does appear that folks have picked up clearly that rCTE's are a profoundly poor choice for counting, not necessarily for other tasks. Incidentally, did you notice during your tests how rubbish the stats are for rCTE's?Wed, 10 Aug 2011 02:06:56 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Jeff Moden (8/9/2011)[/b][hr][quote][b]Dugi (8/9/2011)[/b][hr]Jeff,Thanks for the very nice stuff! Simple to say that I need to read it again and doing more and more practicing and practicing ...!Have a nice day,Dugi[/quote]Hi Dugi,Long time no see. Thanks for stopping by. I've been pretty busy and haven't had a chance to look in at your new users group. How's that going so far?[/quote]I'm busy little bit while I'm trying to organize the first meeting in the September, so things are going good, but still more efforts to make things properly! Thanks for asking we will be in touch for the progress...!Have a nice day!;-)Wed, 10 Aug 2011 00:31:06 GMTDugihttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxExcellent article, as always, Jeff! And as all of your articles that I mange to see and read, this one has been added to my briefcase. I only wish I had the opportunity to work with you for even a few months, my thought processes and SQL code would be so much better for it!Thank you for the time and effort you put into these types of things (articles, forums, etc.).Wed, 10 Aug 2011 00:22:58 GMTKenney Hillhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Dugi (8/9/2011)[/b][hr]Jeff,Thanks for the very nice stuff! Simple to say that I need to read it again and doing more and more practicing and practicing ...!Have a nice day,Dugi[/quote]Hi Dugi,Long time no see. Thanks for stopping by. I've been pretty busy and haven't had a chance to look in at your new users group. How's that going so far?Tue, 09 Aug 2011 20:45:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Mattrick (8/9/2011)[/b][hr]Jeff,Thanks for the article and the performance metrics. After having read it, I will be sending a link to all of the developers in my department as a means to more succinctly explain why I have advocated avoiding recursive CTEs used for this purpose.Thanks again,Matt[/quote]You a Man after my own heart, Matt! :-) rCTE's that count (and some that do other things) look like some pretty clever code and people just don't understand what goes on under the hood of the blasted things. Glad you're spreading the word and thanks for the feedback.Tue, 09 Aug 2011 20:41:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Revenant (8/9/2011)[/b][hr]JeffI have printed this article and put it into my 'keep for frequent reference' folder.Much thanks![/quote]You bet, Revenant. Thank you for the read and the feedback. :-) A person just can't ask for a better compliment than to make it to someone's FAQ folder. Thanks.Tue, 09 Aug 2011 20:37:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]ChrisM@Work (8/9/2011)[/b][hr]Crackin' good read as always Jeff.[/quote]Thanks, Chris. Always good to hear from you.I never noticed it before. Your signature line says you're a defender of moggies. We have 7 that we rescued from the streets. At one time, we had 15 of them, 4 dogs, and a rabbit and that was a little much. Even the dust bunnies got uncomfortable.Nah... didn't get rid of any of them. They all died of old age. I think we've given good lives to something like 40 or 50 of them.Tue, 09 Aug 2011 20:34:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]John Dempsey (8/9/2011)[/b][hr][quote]Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell at PASS. :-P[/quote]Yeah, it has been tough getting time on the forums :(. Makes me sad, but I was definitely looking forward to when this article was coming out. I might have to get a prototype t-shirt going and submit it to Steve Jones for approval. I will be at PASS this year ;) and hopefully meeting you there after you win, so it could work.[/quote]I can only hope to win. There's 4 other good folks I'm competing against. If you'll allow me a self indulgent minute, help spread the word... I can use all the votes I can get. :-)Tue, 09 Aug 2011 20:23:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]tfifield (8/9/2011)[/b][hr]Excellent article Jeff!It really puts the kabosh on the rCTE method. It also shows something that I found a while back. That the old fashioned tally table in some cases can slightly out perform the Itzik style cross join.Todd Fifield[/quote]Hi Todd,Thanks for stopping by.Gosh... I can only hope that the article puts the kabosh on counting with rCTE's in people's minds because, like I said in the article, it's becoming an almost viral problem on posts on many forums. I suspect the "draw" to using the rCTE is that it looks setbased, is easy to write (although not nearly as easy as the two Cross-Joined table methods), and it shows that the person at least knows about a relatively advanced feature. It just doesn't work well for counting problems and writing this article is just my first step to trying to get the word out. I'll also take any help I can get with folks spreading the word.And, I absolutely agree... the Tally Table is right up there with the big boys and it's a whole lot easier to write code with than a lot of the other methods.Again, thanks for stopping by, Todd. Always a pleasure.Tue, 09 Aug 2011 20:19:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]lstrashnoy (8/9/2011)[/b][hr]Of cause your calculations little different:WITH --===== Itzik-Style CROSS JOIN counts from 1 to the number of days needed E1(N) AS ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ), E2(N) AS (SELECT 1000*thousands.N + 100*hunderds.N + 10*tens.N + ones.N FROM E1 thousands, E1 hunderds, E1 tens, E1 ones), cteTally(N) AS (SELECT TOP 4000 N FROM E2 order by N) SELECT * FROM cteTally- Leonard[/quote]Hi Leonard,Thanks for the feedback and the code.You might want to reconsider and use something else, though. On my machine, your code does pretty well at a count of 8k... between 17 and 23 milliseconds (heh... yeah... old machine but I still love it) which is still slower than all the other methods except the rCTE but still pretty fast. The problem is that it also takes almost that long to generate a count of 1. This is because the code generates all 10000 numbers no matter what the requested number is. Take a look at the Execution Plan and then run it in the test code that I attached at the bottom of the article to see what I mean.Tue, 09 Aug 2011 20:04:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxOf cause your calculations little different:WITH --===== Itzik-Style CROSS JOIN counts from 1 to the number of days needed E1(N) AS ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ), E2(N) AS (SELECT 1000*thousands.N + 100*hunderds.N + 10*tens.N + ones.N FROM E1 thousands, E1 hunderds, E1 tens, E1 ones), cteTally(N) AS (SELECT TOP 4000 N FROM E2 order by N) SELECT * FROM cteTally- LeonardTue, 09 Aug 2011 15:16:30 GMTlstrashnoyhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxI believe that if you define E1 in Itzik stile join as:E1(N) AS ( SELECT 0 UNION SELECT 1 UNION SELECT 0 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ),then you don't need an analytic function. That will make your code little cleaner and may be faster.I've been using this technique for years...Thanks,-LeonardTue, 09 Aug 2011 13:00:37 GMTlstrashnoyhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxExcellent article Jeff!It really puts the kabosh on the rCTE method. It also shows something that I found a while back. That the old fashioned tally table in some cases can slightly out perform the Itzik style cross join.Todd FifieldTue, 09 Aug 2011 11:37:32 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote]Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell at PASS. :-P[/quote]Yeah, it has been tough getting time on the forums :(. Makes me sad, but I was definitely looking forward to when this article was coming out. I might have to get a prototype t-shirt going and submit it to Steve Jones for approval. I will be at PASS this year ;) and hopefully meeting you there after you win, so it could work.Tue, 09 Aug 2011 10:28:52 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxCrackin' good read as always Jeff.Tue, 09 Aug 2011 09:50:11 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxJeffI have printed this article and put it into my 'keep for frequent reference' folder.Much thanks!Tue, 09 Aug 2011 09:27:23 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxJeff,Thanks for the article and the performance metrics. After having read it, I will be sending a link to all of the developers in my department as a means to more succinctly explain why I have advocated avoiding recursive CTEs used for this purpose.Thanks again,MattTue, 09 Aug 2011 06:52:38 GMTMattrickhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspxJeff,Thanks for the very nice stuff! Simple to say that I need to read it again and doing more and more practicing and practicing ...!Have a nice day,DugiTue, 09 Aug 2011 05:32:21 GMTDugihttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Andy DBA (8/8/2011)[/b][hr][quote][b]majorbloodnock (8/8/2011)[/b] ... No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes...[/quote]A perfectly acceptable alcoholic drink? Have you ever tasted Canadian "beer"? I'm guessing the whole idea behind the popsicles is to numb the taste buds...[/quote]He, he. Yes I have, and that brings me back to point one of my original post. It isn't really beer, except, perhaps, after someone's processed it. ;-)[quote]Sorry, don't mean to pick on Canadian beer. Most American "beer"s aren't palatable either unless they're too cold to taste. That's why we have a brand where graphics on the can change color when it's cold enough for consumption![/quote]To be honest, it isn't just an issue on the North American continent. Fosters, for instance, may be a different brand, but it's still basically the same gassy chemical brew produced over here.Tue, 09 Aug 2011 04:59:19 GMTmajorbloodnockhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]John Dempsey (8/8/2011)[/b][hr]Great article Jeff with great explanations & examples.[/quote]Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell at PASS. :-PTue, 09 Aug 2011 04:44:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Andy DBA (8/8/2011)[/b][hr]Oh yeah, Great article, Jeff![/quote]BWAA-HAAA!!! Admit it... you only came for the beer! :-PThanks for the read and the feedback, Andy.Tue, 09 Aug 2011 04:39:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1155832-203-1.aspx[quote][b]Evil Kraig F (8/8/2011)[/b][hr]Say Jeff, Excellent examples and walkthroughs as always to not only understand that it's sub-optimal, but WHY, and WHERE, and how to prove it to both yourself and your boss when you need to.The best part about it is that you can take your own code, compare it to the samples, and it helps you check your own work against it with the necessary optimization pieces swapped in/out. One of the best parts of the code your dust bunnies write for you under threat of no more popsicles.Thanks again![/quote]Awesome new handle, Craig. The dust bunnies like it, too! :-) And that's some great feedback. I'm just glad the dust bunnies will work for the occasional beer popsicle . :-P Thanks, Craig.Tue, 09 Aug 2011 04:35:47 GMTJeff Moden