January 6, 2016 at 9:05 am
jaime.simancas 27970 (1/6/2016)
below86 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
In and ideal world/team you would have such 'rules', but good luck enforcing that. Back in my COBOL days we had strict coding rules, but you still found code slip into production that didn't completely follow the rules. SQL, IMHO, is more of a personal preference in coding, you will be lucky to find two people who like to format their code the same way. I've found a lot of people I work with and contractors that all seem to prefer no formatting, indenting, and so on. When you have a ton of projects waiting in the wings corporate doesn't want you spending the time to follow some coding rules, just get it done, and working correctly.
Redgate's SQL Promt <Format Query> comes to mind.
We have to work with the tools corporate lets us have. 🙁
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 6, 2016 at 9:08 am
below86 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
below86 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
below86 (1/6/2016)
I've had to work with a group of contractors for over a year now, and I find their use of CTE's to be excessive, and as you mentioned most are unnecessary. I chalk a lot of it up to these people truly don't understand the data and how to join the tables properly, and yes a group of us have tried to explain it many times. As I work on the code that they wrote I find I tend to remove a lot of the CTE's. I find that CTE's can be useful but they are also over used by some people.ill be honest when is started this thread this is the kind of answer I was hoping for
I am a contractor myself but I don't over use CTEs, in fact I believe they are for use at specific times and one of those times isn't to replace a sub query that is only used once in your code
i'm not saying this is the gospel or the way I do things is the way it should be done, im just saying I cant personally see how this type of use of CTEs really has any benefit at all in a sql query, particularly not with readability
It all comes down to preference and skill level as a team, an individual can write what ever and how ever, its when a team comes into play that the lead must make some rules as to when to use CTEs and when not to.. If the whole team can read subqueries better, then there you go. I'm more of a team lead and have to balance out my team's skill levels to create the right atmosphere for everyone to read all the queries quickly. There are instances for each option.
In and ideal world/team you would have such 'rules', but good luck enforcing that. Back in my COBOL days we had strict coding rules, but you still found code slip into production that didn't completely follow the rules. SQL, IMHO, is more of a personal preference in coding, you will be lucky to find two people who like to format their code the same way. I've found a lot of people I work with and contractors that all seem to prefer no formatting, indenting, and so on. When you have a ton of projects waiting in the wings corporate doesn't want you spending the time to follow some coding rules, just get it done, and working correctly.
you "sound" defensive. I mean no harm. I forget how literal text can "Sound", rules, guidance, support, pointing towards. I'm no dictator, but I do try to enforce teamwork and Foster conversation. Yes, you can't tell people what to do and how to do it, but, we can work together to come up with "Standard rules" we'd like to abide by. Luck is trivial.
My personal opinion in the end is, use what is comfortable to you, get as complex or as simplistic as you'd like. Skill is not defined by how you write a query, but by how you solve the right problem.
Sorry, I didn't mean to come off as defensive, I was just trying to say we have tried at times to have standards or rules, they just seem to gradually fade from everyone's mind, and is hard to enforce. Then again we are human, I'm sure I've strayed from these at times, if not more than that.;-)
Well said, I see that you are very passionate about doing things the right way, and understanding the practical realities of work life. It is a complex subject, to use CTE's or not to use CTE's, that's just one question!!
January 6, 2016 at 9:14 am
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
Skill is not defined by how you write a query, but by how you solve the right problem.
hmmmmmm, seems like a very profound thing to say
and at first I though, yeah I totally agree with that
then thinking about it, I don't really, its doesn't really ring true
you can get the perfect results, even with a query that performs well but if its unmaintainable due to (for example) hundreds of prefixing CTEs 🙂 - does that mean you are as skilled as a developer who solves the same problem with a query that is well structured and performs the same
so how you solve the problem can be judged by how you write the query
My opinion comes from the wide range of people I have had the privileged to work with, in all honesty, most businesses can't afford highly trained experts at writing perfect if not genius SQL. But, I remember I had a boss once, who always wanted the one up, I don't want that, I just want to express an opinion that it's best just to work with the team you have and help them write better code if they are not skilled in "code" writing, and learn from their problem solving skills as that is what really matters. I don't believe in the "Get r Done" mentality, it's just, uneducated.
oh yes, and thank you for such a wonderful and very passionate conversation.
🙂
I totally agree with you btw
if you have a junior developer then nobody is going to shoot him if his code isn't genius
and even if you have a senior developer, everyone writes code that they go back to later and think, what was I thinking when I wrote that pile of poo
January 6, 2016 at 9:17 am
erics44 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
Skill is not defined by how you write a query, but by how you solve the right problem.
hmmmmmm, seems like a very profound thing to say
and at first I though, yeah I totally agree with that
then thinking about it, I don't really, its doesn't really ring true
you can get the perfect results, even with a query that performs well but if its unmaintainable due to (for example) hundreds of prefixing CTEs 🙂 - does that mean you are as skilled as a developer who solves the same problem with a query that is well structured and performs the same
so how you solve the problem can be judged by how you write the query
My opinion comes from the wide range of people I have had the privileged to work with, in all honesty, most businesses can't afford highly trained experts at writing perfect if not genius SQL. But, I remember I had a boss once, who always wanted the one up, I don't want that, I just want to express an opinion that it's best just to work with the team you have and help them write better code if they are not skilled in "code" writing, and learn from their problem solving skills as that is what really matters. I don't believe in the "Get r Done" mentality, it's just, uneducated.
oh yes, and thank you for such a wonderful and very passionate conversation.
🙂
I totally agree with you btw
if you have a junior developer then nobody is going to shoot him if his code isn't genius
and even if you have a senior developer, everyone writes code that they go back to later and think, what was I thinking when I wrote that pile of poo
That is so true, I don't know how many times I've said to myself "who wrote this pile of poo?', only to realize it was me.:-)
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 6, 2016 at 11:07 am
below86 (1/6/2016)
erics44 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
erics44 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
Skill is not defined by how you write a query, but by how you solve the right problem.
hmmmmmm, seems like a very profound thing to say
and at first I though, yeah I totally agree with that
then thinking about it, I don't really, its doesn't really ring true
you can get the perfect results, even with a query that performs well but if its unmaintainable due to (for example) hundreds of prefixing CTEs 🙂 - does that mean you are as skilled as a developer who solves the same problem with a query that is well structured and performs the same
so how you solve the problem can be judged by how you write the query
My opinion comes from the wide range of people I have had the privileged to work with, in all honesty, most businesses can't afford highly trained experts at writing perfect if not genius SQL. But, I remember I had a boss once, who always wanted the one up, I don't want that, I just want to express an opinion that it's best just to work with the team you have and help them write better code if they are not skilled in "code" writing, and learn from their problem solving skills as that is what really matters. I don't believe in the "Get r Done" mentality, it's just, uneducated.
oh yes, and thank you for such a wonderful and very passionate conversation.
🙂
I totally agree with you btw
if you have a junior developer then nobody is going to shoot him if his code isn't genius
and even if you have a senior developer, everyone writes code that they go back to later and think, what was I thinking when I wrote that pile of poo
That is so true, I don't know how many times I've said to myself "who wrote this pile of poo?', only to realize it was me.:-)
I deal with extremely talented individuals daily, very smart, very driven, very factual and logical. poo is common in life, and common table expressions are common in queries. ;p
lol, I am saying you guys are very smart, and I think it shows.
January 6, 2016 at 11:41 am
Sergiy (1/5/2016)
Alan.B (1/5/2016)[hrWhat I was demonstrating was in response to the OP's original question and is why someone would use a CTE instead of doing a direct update... especially when the data modification logic is extremely complex (I did a simple example so that more people would understand my point). See my previous comment.
When data modification logic is extremely complex I would not come anywhere near a CTE.
It's like updating a view - it might work or might not work, but some times you wished it did not work.
To test extremely complex updates I use this simple trick:
UPDATE a
SET c1 = ComplexCalculation(c1),
c2 = AnotherComplexalculation (c3,c4)
/* SELECT a.c1 C1OldValue, ComplexCalculation(c1) C1NewValue,
a.c2 C2OldValue, AnotherComplexalculation (c3,c4) C2NewValue
-- */
FROM Table1 a
Join ... Whatever complex join you need to do ...
WHERE Very complex filtering goes here
Highlight the query starting from SELECT and run it - see what do you update, with what, and how many rows are gonna be updated.
Best part - you see what goes to the specified table, not to the view implemented on-fly by the CTE.
Yeah, this is a less elegant way of doing what I was describing. You're accomplishing the same thing except that, when using a CTE you don't have to change the UPDATE statement to a SELECT statement. UPDATES based on a JOIN BTW are not standard.
it might work or might not work
No, it will work as well as an update that does not use a CTE; check the query plan.
We're not talking about any enhancement here.
As i said, every task which can be done with CTE can be and was done with pre-CTE SQL features.
You can't write a recursive CTE with using pre-CTE features.
Those who know how to design databases and program in SQL - they do not need CTE at all.
This is just silly. Every MVP and Certified Master whose article and/or book I have read uses CTEs. And I use them and I know how to design DBs and program in SQL. Quit while you are behind.
-- Itzik Ben-Gan 2001
January 6, 2016 at 12:11 pm
below86 (1/6/2016)
erics44 (1/6/2016)
everyone writes code that they go back to later and think, what was I thinking when I wrote that pile of poo
That is so true, I don't know how many times I've said to myself "who wrote this pile of poo?', only to realize it was me.:-)
The best is when you go back and look at your own code and it takes 10 mins just to figure out whether you having a stroke of genius when you wrote the original code... Or just a stroke...
January 6, 2016 at 12:18 pm
Jason A. Long (1/6/2016)
below86 (1/6/2016)
erics44 (1/6/2016)
everyone writes code that they go back to later and think, what was I thinking when I wrote that pile of poo
That is so true, I don't know how many times I've said to myself "who wrote this pile of poo?', only to realize it was me.:-)
The best is when you go back and look at your own code and it takes 10 mins just to figure out whether you having a stroke of genius when you wrote the original code... Or just a stroke...
hahahha, so true!!
January 6, 2016 at 12:46 pm
erics44 (1/6/2016)
Lynn Pettis (1/6/2016)
One thing that would be nice is if Microsoft implemented CTEs more like the way Oracle implemented their sub-query refactoring clause. Oracle can treat the sub-query refactoring clause as inline code or it can use it as a temporary table running it only once when used multiple times in a query. A CTE in SQL Server is run separately for each use in a single query.is that right? SQL recompiles the CTE each time it is used?
No, and that's not what Lynn said.
Subqueries and CTEs are both part of a larger statement. Compilation is at the batch level, each statement in a batch gets compiled. If a subquery appears multiple times in a query, it's executed multiple times. If a CTE is referenced multiple times in a query, it's executed multiple times.
These, for example, are identical, will have identical execution plans and will perform identically.
WITH ACTE AS
(SELECT Col1, Col2 FROM SomeTable WHERE SomeOtherColumn = 1)
SELECT * FROM tbl1 t
INNER JOIN ACTE a1 on t.ColA = a1.Col1
INNER JOIN ACTE a2 on t.ColB = a2.Col1
INNER JOIN ACTE a3 on t.ColC = a3.Col1
and
SELECT * FROM tbl1 t
INNER JOIN (SELECT Col1, Col2 FROM SomeTable WHERE SomeOtherColumn = 1) a1 on t.ColA = a1.Col1
INNER JOIN (SELECT Col1, Col2 FROM SomeTable WHERE SomeOtherColumn = 1) a2 on t.ColB = a2.Col1
INNER JOIN (SELECT Col1, Col2 FROM SomeTable WHERE SomeOtherColumn = 1) a3 on t.ColC = a3.Col1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2016 at 1:40 pm
Alan.B (1/6/2016)
This is just silly. Every MVP and Certified Master whose article and/or book I have read uses CTEs. And I use them and I know how to design DBs and program in SQL. Quit while you are behind.
Honest mate, I've worked with qualified and non qualified developers and in my experience you can edge you're bets on quality either way
I use ctes regularly but the original question was about over use of ctes
And I'll be honest I don't think using the cte for an update is more eligant, I would say it's less so and a bit uneccesary
But it is preference as mentioned before 🙂
January 6, 2016 at 2:09 pm
Hugo Kornelis (1/6/2016)
A CTE allows you to define a complex subquery once and refer to it multiple times in the same query without repeating the code. Technically possible before CTE, but cumbersome.
Really?
Ever heard of views?
You might want to read CREATE VIEW article from BOL. Very educating.
:hehe: Just kidding.
If seriously, referring to the same CTE multiple times, especially when it is a complex subquery is not a very smart decision.
CTE re-executed every time it's mentioned in query. Re-executing a complicated subquery creates an unnecessary overhead and affects performance not in a good way.
If you want to reuse a recordset multiple times use good old temporary objects.
And risky - in future maintenance it's too easy to make a change in one place and forget the other place.
Oh, yeah.
Copy-paste programming is way more safe option.
A CTE can be recursive. Impossible before CTE was introduced.
Nice try Hugo!
But you know, I still do not believe your knowledge of SQL is so limited.
🙂
Strictly speaking, the previous statement must be terminated with ";".
ONLY if the next statement starts with CTE.
No. The T-SQL language has always had a semicolon terminator for all statements, but it was optional.
Yes. it must be terminated (emphasis on *must*) only before CTE, THROW and other constructions copied from other languages.
Which indicates that they are - yes, foreign to SQL.
Maybe they would come up with another keyword, if they would be thinking of a new feature for the language named SQL.
But they did not.
They simply copied the construction from another language, for the sake of comfort for those who use to use that construction.
The concept of CTEs and the WITH keyword to introduce it are both defined in the ANSI standard for SQL. (Unfortunately, Microsoft has decided to only partially implement this standard; ANSI defined it as WITH [RECURSIVE], making recursive CTEs easier to recognise).
It does not change the fact - the keyword WITH was already occupied in SQL by query options. There would not be any need to use the same keyword for totally different application if not for the purpose of exact repeating of the construction used in other languages.
English is not such a limited language. It's not my first language, but even I can come up with dozen of other quite acceptable keywords for derived tables.
Sorry, but the very use of the keyword WITH says - CTE is foreign to SQL.
_____________
Code for TallyGenerator
January 6, 2016 at 2:13 pm
jaime.simancas 27970 (1/6/2016)
but CTEs have their place in making these "Sets" easier to read..
Very questionable. Very.
CTE's break the structure of Structured Query Language, moving part of FROM effectively out of the query.
To me - it makes it much more difficult to read and debug.
_____________
Code for TallyGenerator
January 6, 2016 at 2:17 pm
Sergiy (1/6/2016)
jaime.simancas 27970 (1/6/2016)
but CTEs have their place in making these "Sets" easier to read..Very questionable. Very.
CTE's break the structure of Structured Query Language, moving part of FROM effectively out of the query.
To me - it makes it much more difficult to read and debug.
I'm with you on that
Using ctes in place of a sub query is like reading a book and having to keep skipping to the front to read the story in the correct order 🙂
January 6, 2016 at 2:18 pm
Lynn Pettis (1/6/2016)
I find CTEs help when writing complex queries. They allow you to build up a query in steps, finding and isolating the data you need to complete the actual query. Once done, then you can go back and optimize the query eliminating CTEs where appropriate, while still having the original query to compare results to ensure you still return the correct results.
I believe you can replace "CTE" in this statement with "subquery" or "derived table" and it still will be absolutely correct.
_____________
Code for TallyGenerator
January 6, 2016 at 2:21 pm
erics44 (1/6/2016)
Sergiy (1/6/2016)
jaime.simancas 27970 (1/6/2016)
but CTEs have their place in making these "Sets" easier to read..Very questionable. Very.
CTE's break the structure of Structured Query Language, moving part of FROM effectively out of the query.
To me - it makes it much more difficult to read and debug.
I'm with you on that
Using ctes in place of a sub query is like reading a book and having to keep skipping to the front to read the story in the correct order 🙂
huh?
;with cteSomeSelectFromQuery
AS(
select FROM table
)
select from another table join CTE.
I see lots of FROMs...
Joking..
anyways, at this point, I've learned that everyone will nit pick every detail and find reasons why any logic or opinion is wrong.. so, I leave with a smile. Have a good day.
Viewing 15 posts - 61 through 75 (of 161 total)
You must be logged in to reply to this topic. Login to reply