<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Jeff Moden
»
Hidden RBAR: Counting with Recursive CTE's
69 posts, Page 1 of 7
1
2
3
4
5
»
»»
Hidden RBAR: Counting with Recursive CTE's
Rate Topic
Display Mode
Topic Options
Author
Message
Jeff Moden
Jeff Moden
Posted Monday, August 08, 2011 12:13 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923,
Visits: 26,811
Comments posted to this topic are about the item
Hidden RBAR: Counting with Recursive CTE's
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1155832
Henk van den Berg
Henk van den Berg
Posted Monday, August 08, 2011 2:07 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, March 17, 2013 12:31 AM
Points: 161,
Visits: 360
Very insightful article. Thanks.
I have one question, how do you discard the output?
(Anticipating a "slap-to-the-forehead" moment, but asking anyway...)
Best,
Henk
Post #1155866
majorbloodnock
majorbloodnock
Posted Monday, August 08, 2011 2:21 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 1,043,
Visits: 2,944
Sorry, but there's a glaring inconsistency in this article.
No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes. From this I deduce that either
what you've been eating all this time isn't really made from beer or
there isn't really a secret formula, and you're stringing us all along.
Tut, tut, Mr Moden....
Semper in excretia, sumus solum profundum variat
Post #1155869
George J Verras
George J Verras
Posted Monday, August 08, 2011 5:12 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, August 06, 2012 11:54 AM
Points: 3,
Visits: 46
Excellent, Thanks Jeff!
Post #1155915
Jeff Moden
Jeff Moden
Posted Monday, August 08, 2011 7:21 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923,
Visits: 26,811
Henk van den Berg (8/8/2011)
Very insightful article. Thanks.
I have one question, how do you discard the output?
(Anticipating a "slap-to-the-forehead" moment, but asking anyway...)
Best,
Henk
Heh... no problem, Henk. A lot of people miss it. In SQL Server 2005, start by selecting {Tools}{Options}{Query Results}{Results to Grid}. Then, click the {Discard results after execution} check box. In 2005, you may have to open a new query window in SSMS before the setting takes affect. In 2008, (IIRC) the setting is good for the current window and any new windows you open while the box is checked.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post Attachments
DiscardResults.gif
(
565 views,
14.88 KB
)
Post #1156030
Jeff Moden
Jeff Moden
Posted Monday, August 08, 2011 7:23 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923,
Visits: 26,811
majorbloodnock (8/8/2011)
Sorry, but there's a glaring inconsistency in this article.
No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes. From this I deduce that either
what you've been eating all this time isn't really made from beer or
there isn't really a secret formula, and you're stringing us all along.
Tut, tut, Mr Moden....
Heh... because I like American mass-produced beer in pop-top cans, some would say that I, indeed, have no taste buds.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1156032
Jeff Moden
Jeff Moden
Posted Monday, August 08, 2011 7:24 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923,
Visits: 26,811
George J Verras (8/8/2011)
Excellent, Thanks Jeff!
You bet, George. Thanks for stopping by and for the feedback.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1156034
terrance.steadman
terrance.steadman
Posted Monday, August 08, 2011 8:32 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, April 19, 2013 11:06 AM
Points: 174,
Visits: 423
Greetings Jeff,
That was a wonderful and very insightful article about rCTE's and ways to count out of the box. I will have to put some of the pseudo-counters into practice in my scripts.
Thank you for sharing.
P.S.
Beer popsicles are possible if you know the *secret* ingredient to mix to make it become solid.
Post #1156090
CELKO
CELKO
Posted Monday, August 08, 2011 9:19 AM
SSCommitted
Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945,
Visits: 2,782
This was really good. I was surprised that the WHILE loop did so better than the rCTE. I was expecting the loop to be a bit better because it does not have to touch a table except to write an integer, but I expected the optimizer to turn the rCTE into a loop.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1156137
mcannistra
mcannistra
Posted Monday, August 08, 2011 10:26 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, February 12, 2013 7:00 AM
Points: 9,
Visits: 13
Thank you Jeff for your wonderful post.
It has been extremely helpful to achieve faster speed in a test case I was creating on sql azure.
I referenced your post in the Microsoft forum where I had already started a thread before finding your precious info:
http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c368315a-1bf1-4353-b0af-c4cfbb44b2c1
Cheers,
Mario
Post #1156206
« Prev Topic
|
Next Topic »
69 posts, Page 1 of 7
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
