FizzBuzz

Question

FizzBuzz

Viewing 4 posts - 361 through 363 (of 363 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

chillw1nston (1/28/2014)
not sure what your getting at. I guess because it runs recursively and relys on previous result set to generate next which is a more programmatical construct?

Yup.

Would you advocate a cross join approach using system tables instead? Thats all i can think of without looping

Cross join with system tables or cross join with row constructors (SQL 2008+)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

chillw1nston SSC Enthusiast Points: 147 More actions · Answer 2

chillw1nston

SSC Enthusiast

Points: 147

January 28, 2014 at 7:29 am

#1683917

Thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'm not saying that recursive CTEs are bad, wrong, terrible, never use. Just saying be aware of how they behave.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

GilaMonster (1/28/2014)
chillw1nston (1/28/2014)
--Now scalable, set based and added a fizzbuzz!
DECLARE @threshold int = 100
;WITH numbers ( seqnum )
AS ( SELECT 1 AS seqnum
UNION ALL
SELECT seqnum + 1 AS seqnum
FROM numbers
WHERE seqnum < @threshold
)
Recursive CTEs are questionably set-based (will leave it to you to figure out why) and slower than many other methods of row generation.

I agree with Gail. Recursive CTEs that "count" or process individual rows are a form of "Hidden RBAR" that can be much worse than a well written While Loop. Of course, for the production of regular numeric sequences, both should be avoided. Please see the following article for more information and performance comparisions of 3 other methods that absolutely blow Recursive CTE number generators out of the water even for relatively small numbers of rows.

http://www.sqlservercentral.com/articles/T-SQL/74118/

As Gail also suggested, Recursive CTEs do have some good uses and shouldn't be avoided "just because". As with all else in SQL Server, "It Depends".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)