CTE / VIEW

Question

CTE / VIEW

Viewing 12 posts - 16 through 26 (of 26 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Barry, when I said, "your" I meant Milu's. He miscopied yours.

Milu, I actually answered on behalf of a number of other people, including Jeff Moden. If you look at the part of my post where I went over derived tables vs CTEs, you'll see what I mean.

You are right that CTEs have a scope. It's smaller than the scope of a variable, but that is the correct concept. (Variable last for the whole proc/script, and can be passed to other procs/functions as parameters. CTEs only last till their outer query is done.)

There are already samples of both views, CTEs and even a view with a CTE, in this thread. If you need more samples, please post something about what exactly you need in the sample.

On the subject of looping vs using a CTE, in my tests, CTEs are MUCH faster than cursors, and a little faster than a well-built While loop and temp table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

GSquared (8/11/2008)
Barry, when I said, "your" I meant Milu's. He miscopied yours.

Yeah, I knew that Gus. Sorry if it looked like I was responding to you, I think that we were replying at the same time and I didn't see yours until after I had already replied.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 3

rbarryyoung (8/11/2008)
GSquared (8/11/2008)
Barry, when I said, "your" I meant Milu's. He miscopied yours.
Yeah, I knew that Gus. Sorry if it looked like I was responding to you, I think that we were replying at the same time and I didn't see yours until after I had already replied.

Makes sense.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

rosh-396756 SSCrazy Points: 2338 More actions · Answer 4

rosh-396756

SSCrazy

Points: 2338

August 11, 2008 at 10:45 pm

#856488

which is recommended CTEs or Views?

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

Like everything else, it depends... sometimes, neither. 😉

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 6

Milu
-----------------------------------------------------------------------------------------------------------------
Thanks to rbarryyoung..for his clear explanation.....happy to see Sql Guru's Explanations.....
I LOVE SQL SERVER CENTRAL FORUM......

Milu.:), Finally you accepted truth.

Hope you now clear your concepts.

anyways, Nice to see here a Techie Vs. Future Techie Fight...:P

Cheers!

Sandy.

--

GSquared SSC Guru Points: 260824 More actions · Answer 7

rosh (8/11/2008)
which is recommended CTEs or Views?

Both. They do different things. Where they overlap in function, either will work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQL2016 SSC Eights! Points: 990 More actions · Answer 8

hii.. All,

Thanks to all who guide me & clear my concepts...and thanks to them also who added additional point to this topic....

Special thanks to Sandy too, who gives me a position of Future Techie.

Thanks all for this cooperation.

luv's

Milu.:)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Since Views can have CTE's and CTE's can use Views, there is a lot of overlap between them. The real difference between them is where the SQL code resides.

If you want your common SQL table expressions in-line where you can see them, then use CTE's.

If you want your common SQL table expressions in a separate object where you can reuse them, then use Views.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

rosh-396756 SSCrazy Points: 2338 More actions · Answer 10

rosh-396756

SSCrazy

Points: 2338

August 12, 2008 at 9:58 pm

#857152

And in terms of performance?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

rosh (8/12/2008)
And in terms of performance?

Like everything else, it depends... how are the underlying tables index and does the query use them? What size is TempDB and will it need to grow? Any aggregates in the view that are being joined on? Etc, etc, etc. The ONLY way to know is to test. 😉

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

GSquared SSC Guru Points: 260824 More actions · Answer 12

rosh (8/12/2008)
And in terms of performance?

In my tests, the exact same select written as a CTE and a view performs identically for both. Which makes sense.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon