<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Kev Riley
»
Power and Deception of CTEs
28 posts, Page 3 of 3
««
«
1
2
3
Power and Deception of CTEs
Rate Topic
Display Mode
Topic Options
Author
Message
Paul DB
Paul DB
Posted Wednesday, December 03, 2008 9:37 AM
Valued Member
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:07 PM
Points: 60,
Visits: 257
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, December 03, 2008 4:07 PM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 5:37 PM
Points: 31,521,
Visits: 13,855
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, December 03, 2008 4:08 PM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 5:37 PM
Points: 31,521,
Visits: 13,855
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
timothyawiseman
timothyawiseman
Posted Friday, December 05, 2008 5:50 PM
Right there with Babe
Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 748,
Visits: 905
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
Jeff Moden
Jeff Moden
Posted Saturday, December 06, 2008 8:18 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:35 PM
Points: 33,107,
Visits: 27,027
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #615132
timothyawiseman
timothyawiseman
Posted Saturday, December 06, 2008 2:38 PM
Right there with Babe
Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 748,
Visits: 905
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
katesl
katesl
Posted Saturday, December 06, 2008 3:35 PM
Valued Member
Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:08 PM
Points: 60,
Visits: 406
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
Jeff Moden
Jeff Moden
Posted Saturday, December 06, 2008 3:38 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:35 PM
Points: 33,107,
Visits: 27,027
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #615174
« Prev Topic
|
Next Topic »
28 posts, Page 3 of 3
««
«
1
2
3
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
