March 18, 2013 at 5:09 pm
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:
March 18, 2013 at 5:56 pm
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.
Therefore I left the Russian stuff in Russian, and explained the meaning right after it:
If you you do typing in sake of typing - you're wasting your time and wasting time of those who read it
Eugene Elutin (3/18/2013)
In my opinion it cannot be a serious reasoning
Serious??? What said serious?
Go back and check what did I start that post with!
And how did I ended it.
It was a joke from the beginning. Slightly provocative, I must admit, and look - how many fish I've got on that hook!:w00t:
You've been too serious! Relax a little bit. :hehe:
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.
Now, your last sample
I believe I pointed out that I question that article myself.
I wonder why all the CTE believers "did not notice" the 1st link?
Does not match their beliefs?
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?
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.
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.
The CTE use of "WITH" is totally foreign to SQL, it was mindlessly brought across from other languages, with the single purpose - give front end developers a construction they are familiar with to make them more comfortable while cutting cr..py SQL code. 😛
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.
🙂
_____________
Code for TallyGenerator
March 18, 2013 at 5:57 pm
David Webb-200187 (3/18/2013)
From the MS site:A common table expression 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.
When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation.
http://msdn.microsoft.com/en-us/library/ms345368(v=SQL.105).aspx
What about "When the query plan for a common table expression query does not use a spool operator"?
_____________
Code for TallyGenerator
March 18, 2013 at 7:06 pm
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:
March 18, 2013 at 7:48 pm
Just to stick up for the old CTE...
"On-the-fly" tally tables...
CTE method:
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (
SELECT 1 FROM E1 a,E1 b
),
E4(N) AS (
SELECT 1 FROM E2 a,E2 b
),
cteTally (N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT N
FROM cteTally;
Derived Table method (to get the same execution plan, which has been proven to be an excellent performer)
SELECT N
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (
SELECT 1
FROM (
SELECT 1
FROM (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
) a(N), (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
) b(N)
) c(N), (
SELECT 1
FROM (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
) d(N), (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
) e(N)
) f(N)
) g(N)
) DTTally(N)
I know which one is more readable to me (CTE) and which one takes less typing (CTE).
I realise this is a bit off-topic for the original question, but just in case anyone who is unsure about the use of CTEs reads this thread, I feel it important to point out a situation where CTE is both more compact and more readable. 😀
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 18, 2013 at 7:57 pm
Just replace
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
with
SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V(N)
March 18, 2013 at 8:11 pm
Eugene Elutin (3/18/2013)
Just replace
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALLSELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
with
SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V(N)
Like this? (Assuming you have SQL2008 + )
WITH
E1(N) AS ( SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(N) ),
E2(N) AS ( SELECT 1 FROM E1 a,E1 b ),
E4(N) AS ( SELECT 1 FROM E2 a,E2 b ),
cteTally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 )
SELECT N
FROM cteTally;
SELECT N
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (
SELECT 1
FROM (
SELECT 1
FROM
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(N),
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(N)
) c(N), (
SELECT 1
FROM
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(N),
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) e(N)
) f(N)
) g(N)
) DTTally(N);
The CTE version is still much shorter (nearly half the typing) and much easier to read, isn't it?
And be careful you don't drip water on your keyboard 😉
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 18, 2013 at 8:21 pm
Eugene Elutin (3/18/2013)
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.
OK. It's definitely enough for you for today.
Go to sleep.
😀
_____________
Code for TallyGenerator
March 18, 2013 at 8:37 pm
mister.magoo (3/18/2013)
I realise this is a bit off-topic for the original question,
I believe the whole thread by now is off-topic for the original question.
but just in case anyone who is unsure about the use of CTEs reads this thread, I feel it important to point out a situation where CTE is both more compact and more readable. 😀
Only if you're not aware of this method:
SELECT TOP 10000 N
FROM dbo.Tally AS T
ORDER BY N
😛
_____________
Code for TallyGenerator
March 18, 2013 at 8:55 pm
Sergiy (3/18/2013)
Sean Lange (3/18/2013)
Yes I don't think there is a clear cut advantage for either approach as far as performance is concerned. I think it boils down to preference.If you come back to where it's started you'll see it's exactly what I said.
The only difference is as minor as an extra line of code required for syntax sake only.
I'm not so sure about that "in-memory" thing, therefore I did not bring it up, until you guys forced me. 🙂
But I know there is controversy around it.
That's why I prefer (and suggest) not to use CTE where it's not necessary.
Again, it's my preference.
I don't think we disagreed on this point. I joined the conversation simply because you said that more keystrokes is more complex. That to me was completely ridiculous so I joined in.
It makes no sense at all to think that a CTE cannot live at least partially in tempdb. There is no way that the sql team was that short sighted. This is nothing more than extension of the fallacy that temp tables are memory only. It just doesn't make sense.
As for the article discussing CTE's doing a self join, that is a well known performance issue with CTEs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2013 at 9:02 pm
Sergiy (3/18/2013)
mister.magoo (3/18/2013)
I realise this is a bit off-topic for the original question,I believe the whole thread by now is off-topic for the original question.
but just in case anyone who is unsure about the use of CTEs reads this thread, I feel it important to point out a situation where CTE is both more compact and more readable. 😀
Only if you're not aware of this method:
SELECT TOP 10000 N
FROM dbo.Tally AS T
ORDER BY N
😛
Unless you work for an employer or with a system where you have been told you can't use a fixed tally table. We have seen numerous posts by OPs that have been hamstrung by such requirements, so knowing how to build a dynamic tally table is a worthwhile skill to have in ones back pocket.
March 18, 2013 at 9:04 pm
Sergiy (3/18/2013)
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.
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.
The CTE use of "WITH" is totally foreign to SQL, it was mindlessly brought across from other languages, with the single purpose - give front end developers a construction they are familiar with to make them more comfortable while cutting cr..py SQL code. 😛
What are you talking about here? Comparing styles and objects as being the same makes no sense. This is like saying that the color on a report in SSRS and a sql table are the same thing. I fail to understand why you think that CTEs were created so that front end developers can suddenly start writing sql. You seem to have an issue with CTEs because of the syntax surrounding them. I don't understand your logic or your dislike of the construct.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2013 at 9:50 pm
This can be achived by CTE.
1. First, declare CTE which will run same query with top 2 in decending order.
2. Select data from CTE with ID column in ascending order, from lowest value to highest value (i.e. 32, 33)
-------------------------------------------------------------------------------------------------------
;WITH MyData AS (
SELECT TOP 2 *
FROM DBO.NFFeeds
order by ID desc
)
SELECT * FROM MyData
ORDER BY ID
-------------------------------------------------------------------------------------------------------
March 19, 2013 at 4:03 am
Sergiy (3/18/2013)
Eugene Elutin (3/18/2013)
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.OK. It's definitely enough for you for today.
Go to sleep.
😀
Thank you very much! I did sleep well, indeed. 😉
So, you think that there is no much difference in the above sentences?
May be! But not for myself. English is quite precise language, so a small variations sometimes make a difference.
I cannot say that CTE are easier to write than derived tables, as they are actually almost identical.
However, if you take the query as a whole thing, using CTE makes it easier to write as it makes it more readable.
You may not agree on the above, but you should know:
opposite is stupid
😀
March 19, 2013 at 6:32 pm
Oh, I see.
"To write" is "to read.
Aha.
And "black" is "white'.
And "lie" is "truth".
Of course.
I'm recalling I read something like that before...
Or - did I write it?
Oh, I cannot be so sure anymore...
_____________
Code for TallyGenerator
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply