How to improve SET-BASED Logic?

Question

How to improve SET-BASED Logic?

karthik M

One Orange Chip

Points: 29139
More actions
December 21, 2007 at 4:56 am

#91481

Dear Experts,
I have posted some question in this site. Basically , i have used RBAR Logic(While Loop) to get answer for those questions.But,I got SET Based answers for those questions from our sqlservercentral experts.
1) How to improve SET BASED Logic ?Because,If any work came like printing 1 to 10 numbers or displaying string in Pyramid format,then immediately i am starting to think RBAR Logic instead of SET BASED Logic.
Do i have to do any practice ? or will it come by experience ?
karthik

Viewing 5 posts - 1 through 4 (of 4 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

I guess it's just experience. For a lot of the RBAR solutions (now considered problems!!!) that I used to come up with, I can work out the set-based way of doing it. Some of the time I'll start writing RBAR and then the set-based logic will hit me square in the face halfway through. 😀

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

The only suggestion I can make is to assume that every problem has a set based solution up front. Then work on finding it. RBAR becomes a crutch. You just have to begin to force your brain to think that way. I still don't find it easy, but it is easier.

Our developers constantly fall back to RBAR with the explanation, "Well, I wasn't sure how to do it with a set, so I did this instead." Which is kind of amazing because if I went into their projects and created this incredibly huge set of spaghetti code consisting of 10,000 GO TO statements with the explanation "I wasn't sure how to do it with objects, so I did this instead" they'd burn me at a stake.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Grant is spot on... If you want to avoid RBAR, stop thinking RBAR. The key or "secret" to that is to never ever think about what you want to do to a row... always think about what you want to do to a column. Another important part of avoiding RBAR is to remember to "Divide'n'Conquer"... you just don't have to come up with a solution all in a single query.

Sure, there're a couple of tricks that you need to learn like the Tally table in 2k or things like ROW_NUMBER() OVER () and FOR XML PATH in 2k5 to make life simpler. But the key is to think "columns"...

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

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Slowly i started to think SetBased logic.

I have read some posts and articles regarding Setbased and RBAR logic in this site very Recently.

In future,I never use while loop to generate numbers on the fly.

Yes As Mr.Jeff said,I can use

1) spt_values

2) Cross Join with system tables ( Tallly Table)

3) Cross Join with normal query

But I know I need to learn lot of things to improve Setbased logic.

karthik