Well, well! Glad to see that the grand misunderstanding as to what is "Set Based" is and is not, prevails. Odd, too, that I've advocated the use of a particular hint for set based running totals, only to be scoffed at, and now some of those same folks are getting on the bandwagon. 😛

So, here we go again... what is "Set Based" and what is not?

Contrary to popular belief, "Set Based" does NOT mean "all in one query" and not even "all in one view"... at least to me, it doesn't. To me, "Set Based" means "processing one or more sets of information until a final result set is achieved" and "within a single query, that usually means that each row should be "touched" only once as part of a set".

Hmmm... "processing one or more sets of information"... sounds procedureal... is it? Some folks would say so and that's ok with me because that's not RBAR.

Notice, too, that not everything that appears to be set based, actually is. Take this example...

USE NorthWind

SELECT x.OrderID,

x.Freight,

(SELECT SUM(y.Freight)

FROM dbo.Orders y

WHERE y.OrderID <= x.OrderID) AS RunningTotal,

(SELECT COUNT(y.Freight)

FROM dbo.Orders y

WHERE y.OrderID <= x.OrderID) AS RunningCount

FROM dbo.Orders X

ORDER BY x.OrderID

There's no WHILE loop and no Cursor... certainly that must be set based? If you think so, then take a look at the following article and see why it is not and how it can cripple a server with a relatively small number of rows...

[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]

As you said, "The issue here isn't the set-based solution, it's a poorly built solution. It's a lack of knowledge and understanding of basic query structure and optimizer functionality,"... truer words could not have been stated.

Last but not least, I get a little ticked when people say that set based code is more difficult to read than "procedural" code (they really mean RBAR code). They're obviously talking about things like correlated subqueries and cross-apply's and the like, neither of which necessarily constitute set based code. The whole idea of set based code is to keep things simple and proper set based methods usually show that way in code, as well. Consider the simple act of counting from 1 to 10...

...using a loop...

--===== Create and preset a loop counter

DECLARE @Counter INT

SET @Counter = 1

--===== Count from 1 to 10

WHILE @Counter <= 10

BEGIN

SELECT @Counter

SET @Counter = @Counter + 1

END

...using set based...

--===== Count from 1 to 10

SELECT N

FROM dbo.Tally

WHERE N <= 10

Even one of the most difficult things in the world to do in SQL, do a running update where each row is based on calculations done including the previous row, can quickly be done in a set based manner using "pseudo cursors" and a "hint"... for more on those, see the following article...

[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/font][/url]

Steve, you mentioned.... "In this post, Linchi mentions that sometimes simply following the advice of always building a set-based solution leads to code that might not perform as well as a procedural solution".

Heh... I'll admit that "simply following the advice" of others may sometimes lead you down the primrose path, but finding the CORRECT set based solution will likely not lead you to something that doesn't perform as well. There are very, very few exceptions. As you said, "I would agree with that, but I'd mention that the problem with most peoples' code is that they don't really understand what or why they write something a certain way." That's 100% spot-on, Steve...

So far as the "set based limit" goes... here's my quote for the day... "Computers (and T-SQL) are an imagination limited tool... If you have a limited imagination, you will limit the tool." For me, there is no such thing as a "set based limit".