Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Power and Deception of CTEs Expand / Collapse
Author
Message
Posted Wednesday, December 3, 2008 9:37 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 9, 2013 1:26 PM
Points: 60, 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
Post #612974
Posted Wednesday, December 3, 2008 4:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:53 PM
Points: 31,177, Visits: 15,623
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
Post #613265
Posted Wednesday, December 3, 2008 4:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:53 PM
Points: 31,177, Visits: 15,623
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
Post #613266
Posted Friday, December 5, 2008 5:50 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #615037
Posted Saturday, December 6, 2008 8:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #615132
Posted Saturday, December 6, 2008 2:38 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #615167
Posted Saturday, December 6, 2008 3:35 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:20 PM
Points: 63, Visits: 470
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.."
Post #615172
Posted Saturday, December 6, 2008 3:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #615174
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse