RBAR

Question

RBAR

Viewing 6 posts - 16 through 20 (of 20 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

goofbauer (11/8/2011)
I now have a new favorite. :blush: The Itzik-style cross join. Begs the question, why does MS place the emphasis they do on cursors and rCTEs in certification. A bit like giving loaded guns to children, don't you think?

Ha! Perhaps. But recursive CTEs have their uses, and so too do cursors (though good uses of cursors are the rarer of the two). There are very few absolutes in the SQL Server world, so a good part of being 'good' at SQL is knowing which tool to use in which situation, and why...

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

BSavoie SSCertifiable Points: 6309 More actions · Answer 2

Thanks for the great ideas!

I think I'll try creating the tally table on the fly approach. I'm afraid my process might not be able to access master in production, so the spt_values my not work for me.

.

goofbauer Mr or Mrs. 500 Points: 571 More actions · Answer 3

BSavoie (11/8/2011)
I'm afraid my process might not be able to access master in production, so the spt_values my not work for me.

Frankly with these approaches, you can use any table that is sure to have more records than you need. I think it's a matter of being selective.

The alternative is the approach mentioned in the article SQL Kiwi linked above:

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

Here's the snippet from that article:

WITH

E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), -- 1*10^1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows

E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 1*10^8 or 100,000,000 rows

SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8

;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Just for another example, you could also read this article: http://www.sqlservercentral.com/articles/T-SQL/67899/

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

goofbauer (11/8/2011)
I now have a new favorite. :blush: The Itzek-style cross join. Begs the question, why does MS place the emphasis they do on cursors and rCTEs in certification. A bit like giving loaded guns to children, don't you think?

BWAAA-HAAAA!!! It's because they don't know what a Tally Table is. 😀

--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)

BSavoie SSCertifiable Points: 6309 More actions · Answer 6

Yes Jeff, you are exactly right! That is the problem for me.

.