SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Power and Deception of CTEs


Power and Deception of CTEs

Author
Message
Paul DB
Paul DB
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 258
Christopher Stobbs (12/2/2008)
Good point Steve, I was asking just yesterday about the left join vs NOT Exists, and well Gail gave me a very good visible answer and well ... like you say just cause it looks good doesn't mean it is good :-)

Can someone please post the link to the thread Steve and Chris was talking about? Thanks.

Paul DB
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62432 Visits: 19102
Ah, was afraid you would ask that. I'll look for it.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62432 Visits: 19102
tada - http://www.sqlservercentral.com/Forums/Topic611416-338-1.aspx

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
timothyawiseman
timothyawiseman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1224 Visits: 920
G Bryant McClellan (12/1/2008)
Jeff Moden (12/1/2008)
Good article... but, because of the title, I was actually expecting to see something about a problem or technique with CTE's that couldn't be done using other forms of code. The article is really about how an index can help any query be it a CTE, Derived Table, View, etc.


Jeff,

I was also thrown by the title although 'deception' gave me a clue.

On the other hand, thanks to Kev for pointing out what should have been obvious...the use of indexes on columns commonly used in WHERE and ORDER BY clauses. I must admit that it gave me ideas on some inherited problem code that is built with CTE for no reason I can discern. The CTE is (to me) no gain over standard SQL but potentially a gauze curtain covering up an underlying indexing issue. And I would venture that it was authored by a procedural programmer...


CTEs really bring two things tot he table that subqueries/derived tables do not. One is the ability to use recursion. It is definitely much maligned, and should be used with care, but it does have its uses.

More significantly, it can improve readability. It helps make it easier to read from top to bottom and it can move complexity from the "From" clause to a less cluttered area. To slightly modify a quote from Larry Wall, "SQL is designed to give you several ways to do anything, so consider picking the most readable one."

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85929 Visits: 41091
timothyawiseman (12/5/2008)
To slightly modify a quote from Larry Wall, "SQL is designed to give you several ways to do anything, so consider picking the most readable one."


I'm definitely for that and glad to see anyone concerned about readability for a change... but even CTE's won't make readable code without a bit of concern for formatting and commenting. Heh... I had one guy at my previous job that thought it was a good formatting choice to write each query on a single line and all lower case.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
timothyawiseman
timothyawiseman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1224 Visits: 920
Jeff Moden (12/6/2008)
timothyawiseman (12/5/2008)
To slightly modify a quote from Larry Wall, "SQL is designed to give you several ways to do anything, so consider picking the most readable one."


I'm definitely for that and glad to see anyone concerned about readability for a change... but even CTE's won't make readable code without a bit of concern for formatting and commenting. Heh... I had one guy at my previous job that thought it was a good formatting choice to write each query on a single line and all lower case.


You have a point. But, in my opinion, a well formatted CTE will be easier to read and understand than even a well formatted derived table inside some other portion of the command.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
katesl
katesl
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 473
So application developer A writes the CTE -- or the derived table -- then application developer B writes it slightly differently a month later in another application. Modularity? No, maintenance nightmare. There should be a DBA writing views, verifying with business analysts that the views select exactly what is expected, documenting the views, and reviewing the SQL written by the application developers to make sure they use standard views -- instead of developing as if their programming creativity is what it's about.

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85929 Visits: 41091
timothyawiseman (12/6/2008)
Jeff Moden (12/6/2008)
timothyawiseman (12/5/2008)
To slightly modify a quote from Larry Wall, "SQL is designed to give you several ways to do anything, so consider picking the most readable one."


I'm definitely for that and glad to see anyone concerned about readability for a change... but even CTE's won't make readable code without a bit of concern for formatting and commenting. Heh... I had one guy at my previous job that thought it was a good formatting choice to write each query on a single line and all lower case.


You have a point. But, in my opinion, a well formatted CTE will be easier to read and understand than even a well formatted derived table inside some other portion of the command.


Heh... AGREED! Ephasis on "well formatted". You and I are saying the same thing!

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search