Sergiy (3/18/2013)
Eugene Elutin (3/18/2013)
First of all it's not quite polite to put statements in foreign language on English forum without translation. So, here we are:As you pointed out this proverb does not make much sense for non-Russians.
I'm sure your long explanation did not help a lot here.
Actually, it only gave a wrong impression about Russians and wrong meaning to the proverb.
I would love to know the right one. I don't know, but what I've quoted in English is well in line with some Russian linguists finding http://commbehavior.narod.ru/RusFin/RusFin2000/Sternin4.htm
:exclamation: non-English site.
You've been too serious! Relax a little bit. :hehe:
I'm hardly ever serious. But thanks, I'm going to jacuzzi for relaxation, a bit...
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.
It's not what Lynn said, and what I responded on.
He stated that CTE are easier to write than derived tables.
You may go back and check.
Nothing about readability.
I did. Checked. He did not state that CTE are easier to write. For a mater of fact, he said that writing queries using CTE is easier than using derived tables. I tend to agree as CTE helps code readability (I agree that Lynn didn't mentioned this point there).
Now, your last sample
I believe I pointed out that I question that article myself.
Great we have something in common (apart of mother tongue :hehe:)
I wonder why all the CTE believers "did not notice" the 1st link?
Does not match their beliefs?
I'm agnostic. So, don't hold much of any beliefs...
I've looked into your first one. This one is even worse than second.
There is no sample of code which allows author to claim that
You can simply see that a CTE does not write anything to the tempdb, hence it has a higher performance compared with the other two options.
At the end, if there is enough memory for use by SQL Server, regardless of what you are using temp tables, table variabes or CTE, you will see no writes into tempdb (except ones to create references in its sys.objects for #tables and table variables). If there is not enough memory - SQL Server will use tempdb to store the data.
Actually author calling CTE's as "temporary views" - I never heard anything like that before.
Just using standard MS description from BoL:
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
So, I prefer to call it as MS - temporary result set.
The second line I've marked in bold is another point. CTE is like derived table, especially it will be for the sample author of the above article talking about. Therefore, he would see the same performance while using derived tables.
And just while I don't forget. Have you seen an example where recursive CTE is used to find distinct values and does it faster than standard SELECT DISTINCT?
Declare styles (objects in OO languages), fill them up and then refer in the following code.
That's how you code CSS, that's how CTE code is formed.
I almost wanted to use some rude word here, but I've behaved... :hehe:
So, I simply say that I strongly disagree with you on this. CTE is not formed the same way as objects in OO languages.
Why it's not in line with SQL best practices?
Because SQL operates with databases.
Where all the objects are already declared (with various CREATE sratements) and populated (with INSERT/UPDATE/DELETE) statements.
Even the keyword used for CTE.
Type "WITH" in BOL and see what articles it will show to you.
Type "FROM" in Google and see what articles it will show to you.:hehe:
The CTE use of "WITH" is totally foreign to SQL...
It's not. Common Table Expressions are part of ANSI SQL 99, or SQL3.
Since 1999 the SQL standard allows named subqueries called common table expression (named and designed after the IBM DB2 version 2 implementation; Oracle calls these subquery factoring). CTEs can be also be recursive by referring to themselves; the resulting mechanism allows tree or graph traversals (when represented as relations), and more generally fixpoint computations.
Say it to Leo Tolstoy :hehe:
Actually, Tolstoy did not write a line for no reason.
His code is just extremely well in-line documented.
🙂
O'Yes he did and plenty! There are quite few literature critics agree on this and even call him graphoman!
Graphomania (from Greek ??afe?? — writing, and µa??a — insanity), also known as scribomania, refers to an obsessive impulse to write.
That's enough for today, it's 5 past 1 am here. Time for jacuzzi and good sleep! :hehe: