Sergiy (3/18/2013)
Eugene Elutin (3/18/2013)
:w00t::w00t::w00t::w00t::w00t:"extra words" is a greatest argument to declare a "code" winner I'have ever seen.
Did you find anything else different?
Anything?
If you missed it - I responded on this statement:
Lynn Pettis (3/17/2013)
Really, CTEs are complex? I find them to make writing queries easier as you don't have to write derived tables.So, I compared those 2 ways of writing queries.
Having no other points of differece, less typing makes DT queries easier to write.
Extra typing always makes anything more complex.
By definition.
There is no such definition! Actually, the opposite is quite more common.
Opposite is stupid.
Smeh bez prichiny - priznak durachiny.
If you you do typing in sake of typing - you're wasting your time and wasting time of those who read it (including SQL optimizer).
There must be a reason to add an extra line to a code.
Inline documentation, better formatting, whatever else - everytime there must be a reason.
I cannot see any advantage of that particular extra line.
Can you point on it?
From my experience, most of developers finding CTE is much more clear way to write query and therefore less complex then using "derived tables".
Most developers used to wrire "CSS style" code.
CTE fits there patterns and allows not to learn SQL-specific best practices.
That's why it's so popular.
First of all it's not quite polite to put statements in foreign language on English forum without translation. So, here we are:
Smeh bez prichiny - priznak durachiny.
Translates to: "Laughing without reason is a sign of foolishness".
Very Russian one. That what I've found on this subject:
16. Russians don’t smile out of politeness. It’s considered in poor taste to smile without a reason. A constant polite smile is considered a “smile on duty” in Russia and shows people’s insincerity, and unwillingness to show real feelings.
17. Moscow State University Facebook •It’s not typical for Russians to smile at a person whose eyes meet by chance •A Russian does not normally give a smile in return •Russians do not smile when working or doing something serious (Levine and Adelman 1993)
18. A Russian smile is a sign of personal attraction “It’s a complicated relationship” A Russian smile shows that a smiling person likes you or they are close to you. Russians do not normally show that type of affection to strangers.
19. Indian Prime Minister Manmohan Singh In Russia a smile is the expression of either high spirits or a good attitude to a partner. A Russian needs a reason to smile, which is evident to others. It gives a person the right to smile from others’ point of view. The Russian language has the unique proverb missing in other languages, “Laughter without reasons is the sign of foolishness or psychological problems.” (Wierzbicka 1998)
But actually I would argue that this is not really Russian custom but a Soviet one. As after just few years of life in other countries (Australia and UK), I have easily learned to smile to other people and without any reason. And I'm proud of it! 😀
Now back to SQL subject. What really made me "laughing" (actually :w00t: does not stay for laughing) is the way Sergiy reasoned why one syntax wins over another: "extra words".
In my opinion it cannot be a serious reasoning (and on this subject there are actually two opinions exist: my one and wrong one! :hehe:).
There must be a reason to add an extra line to a code.
Why would you add comments into code (if you do)? Make it a bit more clear? So, you can take use of CTE for the same reason. It does, very often, makes code easier to read and understand without making any negative impact for performance.
Of course, the discussed example is trivial enough to be written in any form without much difference (equal query plans just confirms that), but if you take for example more complicated "derived table" cases, then use of CTE significantly improves code readability and therefore maintainability.
Don't try to argue here or I will use your words:
Opposite is stupid.
Now, your last sample (reference http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx ) of performance issues with CTE. If Tony R. instead of CTE used "derived tables" he would have exactly the same problems. Using temp tables in such cases (large transactional data) very often improves performance significantly.
Actually what "CSS style" code is about? I have never heard this terminology in relation to T-SQL.
Also, could you please advise where I can find anything on "SQL-specific best practices" where it says that CTE is not part of them?
And on the bright note:
If you you do typing in sake of typing - you're wasting your time and wasting time of those who read it
Say it to Leo Tolstoy :hehe: