January 6, 2016 at 2:08 am
Sergiy (1/5/2016)
The fact is - CTE is a foreign construction in SQL.
Is that a problem? Should the language be kept in a glass case and never added to or enhanced?
Before you start arguing that - try to explain why CTE construction must be prefixed with ";" - there is no such requirement for any other language construction.
Probably because the keyword "WITH" has a totally different meaning in SQL, so there must be a way to distinguish a "WITH" as a query option from a "WITH" as a CTE definition.
Strictly speaking, the previous statement must be terminated with ";". When the statement with the CTE is the first in the batch, there is no requirement for a semicolon. But I think you answered your own question. Maybe a new "CTE" keyword should have been introduced for use instead of WITH. Would you have been any less opposed in those circumstances?
Some might see a benefit in "doing everything in one query", but I don't see it as a benefit at all. I still appreciate structured programming, you know. 😛
As I and others have said, it's a matter of preference. I don't think either way is right or wrong in most circumstances, but I can't understand your objections about foreignness and semicolons.
John
Edit - posted before I'd finished writing!
January 6, 2016 at 2:16 am
Sergiy (1/5/2016)
try to explain why CTE construction must be prefixed with ";" - there is no such requirement for any other language construction.
THROW - Previous statement must be ; terminated (same as CTE). Worse than CTE in that you don't get an error it the ; is left out, just incorrect behaviour.
MERGE - Must itself be ; terminated
They're not the only ones
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 2:56 am
John Mitchell-245523 (1/6/2016)
Should the language be kept in a glass case and never added to or enhanced?
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.
CTE did not add anything to SQL.
the only purpose pof it - allow .Net developers not to learn how to do things in SQL and use familiar constructions from VB.
Strictly speaking, the previous statement must be terminated with ";".
ONLY if the next statement starts with CTE.
Not to mention - statement separator ";" was a copy-paste from .Net environment as well, brought to SQL together with CTE.
Another absolutely useless feature, not anything to the language, not resolving any problems.
Except for allowing .Net developers not to stumble over sysntax errors when they finish statements with ";" just due to the habit.
But I think you answered your own question. Maybe a new "CTE" keyword should have been introduced for use instead of WITH. Would you have been any less opposed in those circumstances?
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.
And if they would change the keyword - it would not be a familiar construction anymore. Any reason for the excersise would disappear.
As I and others have said, it's a matter of preference. I don't think either way is right or wrong in most circumstances, but I can't understand your objections about foreignness and semicolons.
Exactly - matter of preference.
Those who know other languages better than SQL they prefer to use CTE. it's simply more comfortable for them.
Those who know how to design databases and program in SQL - they do not need CTE at all.
I do not understand your objections about using native SQL constructions instead of introduced foreign ones. 😎
_____________
Code for TallyGenerator
January 6, 2016 at 3:03 am
GilaMonster (1/6/2016)
Sergiy (1/5/2016)
try to explain why CTE construction must be prefixed with ";" - there is no such requirement for any other language construction.THROW - Previous statement must be ; terminated (same as CTE). Worse than CTE in that you don't get an error it the ; is left out, just incorrect behaviour.
MERGE - Must itself be ; terminated
They're not the only ones
Should I refer you to the origins of those keywords?
I bet you know where they were copied from without any hint from me. 🙂
And it was not a best move (another not a best move) from MS.
Introducing "programming by exception" tecnics to a language which is not expected to have any communication with UI - can you think of any more stupid idea?
_____________
Code for TallyGenerator
January 6, 2016 at 3:23 am
Sergiy (1/6/2016)
Those who know other languages better than SQL they prefer to use CTE. it's simply more comfortable for them.Those who know how to design databases and program in SQL - they do not need CTE at all.
Sweeping statements, those. I like to think that I know a bit about database design and programming - I certainly don't know any other languages better. And yet I still prefer CTEs in many cases. I don't care about the history or the reason for the syntax - if it works better for me, I'll use it.
I do not understand your objections about using native SQL constructions instead of introduced foreign ones. 😎
That's because I don't have any. I use what I prefer, but I certainly don't object to your use of what you prefer.
John
January 6, 2016 at 3:28 am
Sergiy (1/6/2016)
As i said, every task which can be done with CTE can be and was done with pre-CTE SQL features.CTE did not add anything to SQL.
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. And risky - in future maintenance it's too easy to make a change in one place and forget the other place.
A CTE can be recursive. Impossible before CTE was introduced.
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. And since SQL Server 2005, omitting the semicolon is officially deprecated, meaning that Microsoft has reserved the right to make it mandatory. That has so far only been done for a few new commands, but the official documentation has already said for more than ten years now that we should all get in the habit of terminating all statements with semicolons.
Not to mention - statement separator ";" was a copy-paste from .Net environment as well, brought to SQL together with CTE.
Another absolutely useless feature, not anything to the language, not resolving any problems.
If you go out on a rant, check your facts first.
The semicolon character to separate statements was already defined in the SQL-92 standard (the oldest standard I have access to). And it was already mandatory in that version. T-SQL was the odd one out by allowing users to omit the semicolon.
And if you think that making the semicolon mandatory does not resolve any issues, try building a parser for a language like SQL and you'll how much easier (read: less hard) it becomes if you have a simple way to determine where a statement ends and the next one starts.
Except for allowing .Net developers not to stumble over sysntax errors when they finish statements with ";" just due to the habit.
So in which version of SQL Server did you actually get errors when using semicolons? As far as I know, the semicolon has always been supported.
But I think you answered your own question. Maybe a new "CTE" keyword should have been introduced for use instead of WITH. Would you have been any less opposed in those circumstances?
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).
I do not understand your objections about using native SQL constructions instead of introduced foreign ones. 😎
I do not understand what prejudice makes you think that new constructions are foreign. Some are, but a lot of the constructions that have been introduced since SQL Server 2005 have actually already been defined in the ANSI standards for SQL long before MS implemented them.
January 6, 2016 at 3:37 am
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. And risky - in future maintenance it's too easy to make a change in one place and forget the other place.
A CTE can be recursive. Impossible before CTE was introduced.
this is exactly the reason for the thread
A CTE allows you to define a complex sub query once that can be referred to multiple times in the same query
if the CTE/Subquery is used multiple times then that is a good way to use a CTE
if a CTE/Subquery is used once then do you use a CTE or a subquery?
I personally prefer a subquery in most situations, with a particularly complex query then a CTE can help, but generally code is more readable if the code occurs in the place it is used within the query
January 6, 2016 at 7:13 am
Now, I think the question you are asking is what preference is on CTE use?
If so, a common answer is, for simplicity. To create a more readable script instead of using large sub queries and also very useful for window functions and aggregates prior to doing some other formulas of from those sets.
Also, another note, USE SET THEORY to solve your questions, in essence, working with "Data Sets" Joins of sets, is always better and faster, but CTEs have their place in making these "Sets" easier to read..
"Solve the right problem, but first find the right problem to solve."
January 6, 2016 at 7:22 am
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.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 6, 2016 at 7:46 am
Yes, you will find queries which live all over the spectrum. again, sticking to Set Theory and making code easy to read, that is key.
January 6, 2016 at 7:59 am
jaime.simancas 27970 (1/6/2016)
Now, I think the question you are asking is what preference is on CTE use?If so, a common answer is, for simplicity. To create a more readable script instead of using large sub queries and also very useful for window functions and aggregates prior to doing some other formulas of from those sets.
Also, another note, USE SET THEORY to solve your questions, in essence, working with "Data Sets" Joins of sets, is always better and faster, but CTEs have their place in making these "Sets" easier to read..
"Solve the right problem, but first find the right problem to solve."
thanks for the reply
ill just mention again this isn't a who's right and who's wrong question so its just my opinion
but I rarely come across a situation where a CTE is more "Readable" than a sub query
a large CTE is the same as a large subquery isn't it? just in a different place in your code
and if your subquery is so large that it isn't readable then perhaps theres an issue that wouldn't be solved with the question - should I use a subquery or should I use a CTE?
January 6, 2016 at 8:02 am
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
January 6, 2016 at 8:05 am
erics44 (1/6/2016)
jaime.simancas 27970 (1/6/2016)
Now, I think the question you are asking is what preference is on CTE use?If so, a common answer is, for simplicity. To create a more readable script instead of using large sub queries and also very useful for window functions and aggregates prior to doing some other formulas of from those sets.
Also, another note, USE SET THEORY to solve your questions, in essence, working with "Data Sets" Joins of sets, is always better and faster, but CTEs have their place in making these "Sets" easier to read..
"Solve the right problem, but first find the right problem to solve."
thanks for the reply
ill just mention again this isn't a who's right and who's wrong question so its just my opinion
but I rarely come across a situation where a CTE is more "Readable" than a sub query
a large CTE is the same as a large subquery isn't it? just in a different place in your code
and if your subquery is so large that it isn't readable then perhaps theres an issue that wouldn't be solved with the question - should I use a subquery or should I use a CTE?
I agree, and it's definately not about who's correct or better. Based on experience, for each individual question one must find the right balance. For my business query writers, I have them write CTE's so they can "Step" through their questions, then we work on optimizations. Sometimes dropping a few CTE's and using set based queries works better. But they have definately found it easier to read and use vs. sub queries. I would think it has to do with the skill level honestly, what ever we end up using.
January 6, 2016 at 8:09 am
Sergiy (1/5/2016)
The fact is - CTE is a foreign construction in SQL.It was copied to T-SQL from front end languages to create more comfortable environment for those ".Net developers with SQL skills". So they do not need to learn how to do in SQL what they use to do using property grouping for objects/lists.
Before you start arguing that - try to explain why CTE construction must be prefixed with ";" - there is no such requirement for any other language construction.
Probably because the keyword "WITH" has a totally different meaning in SQL, so there must be a way to distinguish a "WITH" as a query option from a "WITH" as a CTE definition.
There are no tasks which could be done with CTE but not with pre-CTE language features.
Funny, that in Oracle it's named as "subquery factoring" - pretty self-confessing.
Some might see a benefit in "doing everything in one query", but I don't see it as a benefit at all. I still appreciate structured programming, you know. 😛
This from above is completely wrong:
Before you start arguing that - try to explain why CTE construction must be prefixed with ";" - there is no such requirement for any other language construction.
The CTE construct does NOT begin with a semicolon. The documentation specifically states that the PRECEDING statement must be terminated with a semicolon. The reason people start a CTE with a semicolon are two-fold. One the Microsoft examples in Books Online wrongly start CTEs with a semicolon. And two, it is crutch for people to not properly write their T-SQL code.
Microsoft has even deprecated NOT terminating statements with a semicolon. This means SQL developers working with MS SQL Server should be getting into the habit of terminating all statements with a semicolon.
January 6, 2016 at 8:12 am
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 sounds like we would work well with each other. 🙂 Too bad you aren't one of the contractors I have to deal with. I to don't find most of them more readable. But then again I would more lean sometimes in creating a table or temp table if I had what some are mentioning as a large CTE or sub query.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 15 posts - 31 through 45 (of 161 total)
You must be logged in to reply to this topic. Login to reply