SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Views


Views

Author
Message
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5040 Visits: 2371
Oops, I glossed over that before. Thanks Hugo!
Trey Staker
Trey Staker
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1266 Visits: 2788
Good straight forward question! Thanks!

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Julie Breutzmann
Julie Breutzmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 1921
Hugo Kornelis (4/21/2010)
Kenneth Wymore (4/21/2010)
Does anyone have a link to a view that uses a CTE in its definition?

Go to the page referenced in the explanation of the question; scroll all the way down to the community content; check out the second example.


I usually just look at help from Management Studio. I didn't realize that the website had community content. Thanks for pointing that out.
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 1813
Good question! Thank you Ron.

About 2 years ago I read the BOL reference article on common table expressions and since that time I clearly remember that it stated in the remarks section that "When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon". Since that time I got into habit of always starting the cte expression with a semicolon, i.e.

;with records (some_columns) as
(
select ...
)


Because of that reference I answered today's question incorrectly figuring that surely the statement like this:

create view view_name as
;with records (some_columns) as
(
select ...
) -- etc


will never fly due to that semicolon. This taught me a lesson to read the BOL remarks more carefully. The one about semicolon talks about the batch but if the view definition has only one statement in it then no semicolon is required before the cte.

Oleg
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31918 Visits: 18551
Thanks Ron. This was a good question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10819 Visits: 11967
Oleg Netchaev (4/21/2010)
Since that time I got into habit of always starting the cte expression with a semicolon

I never understand that habit. The semicolon is a statement terminator, not a statement starter, so the logical place for it is at the end of a statement, not before the next one.

The semicolon has always been part of the Transact SQL syntax, but unlike almost every other language, it was optional. And hence, nobody ever used it. This first changed when the SQL Server 2005 parser required a statement to be terminated in order to recognise WITH as the CTE starter, not as a query hint. But in SQL Server 2008, the SQL team went a step further and added "Not using a statement terminator for Transact-SQL statements" to the list of deprecated features. This means that in some future version, you will be required to terminate all statements.

So do as I do - start to accustom yourself to terminating all statements with a semicolon today. You'll be thankful later. (And you'll never have to worry about the semicolon before a CTE anymore).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 1813
Hugo Kornelis (4/21/2010)
Oleg Netchaev (4/21/2010)
Since that time I got into habit of always starting the cte expression with a semicolon

I never understand that habit. The semicolon is a statement terminator, not a statement starter, so the logical place for it is at the end of a statement, not before the next one.

The semicolon has always been part of the Transact SQL syntax, but unlike almost every other language, it was optional. And hence, nobody ever used it. This first changed when the SQL Server 2005 parser required a statement to be terminated in order to recognize WITH as the CTE starter, not as a query hint. But in SQL Server 2008, the SQL team went a step further and added "Not using a statement terminator for Transact-SQL statements" to the list of deprecated features. This means that in some future version, you will be required to terminate all statements.

So do as I do - start to accustom yourself to terminating all statements with a semicolon today. You'll be thankful later. (And you'll never have to worry about the semicolon before a CTE anymore).


Thank you Hugo,

I have been using the semicolons in every statement for at least 2 years now and I try not to fail on this one. I can see that the guideline about placing one before the cte makes sense though. This recommendation is caused precisely by the fact that majority of people still don't use semicolons, so placing one before cte definition is simply to assure that no errors are introduced by someone forgetting to terminate their statement with semicolon. Typical example would be when different developers submit new scripts, one of such scripts begins with cte, and if the previous one was written by someone who did not terminate his last statement then the error will be introduced into the final script should all the small ones be combined.

Oleg
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31918 Visits: 18551
Oleg Netchaev (4/21/2010)
Hugo Kornelis (4/21/2010)
Oleg Netchaev (4/21/2010)
Since that time I got into habit of always starting the cte expression with a semicolon

I never understand that habit. The semicolon is a statement terminator, not a statement starter, so the logical place for it is at the end of a statement, not before the next one.

The semicolon has always been part of the Transact SQL syntax, but unlike almost every other language, it was optional. And hence, nobody ever used it. This first changed when the SQL Server 2005 parser required a statement to be terminated in order to recognize WITH as the CTE starter, not as a query hint. But in SQL Server 2008, the SQL team went a step further and added "Not using a statement terminator for Transact-SQL statements" to the list of deprecated features. This means that in some future version, you will be required to terminate all statements.

So do as I do - start to accustom yourself to terminating all statements with a semicolon today. You'll be thankful later. (And you'll never have to worry about the semicolon before a CTE anymore).


Thank you Hugo,

I have been using the semicolons in every statement for at least 2 years now and I try not to fail on this one. I can see that the guideline about placing one before the cte makes sense though. This recommendation is caused precisely by the fact that majority of people still don't use semicolons, so placing one before cte definition is simply to assure that no errors are introduced by someone forgetting to terminate their statement with semicolon. Typical example would be when different developers submit new scripts, one of such scripts begins with cte, and if the previous one was written by someone who did not terminate his last statement then the error will be introduced into the final script should all the small ones be combined.

Oleg


I still disagree. This is used as a catch-all to avoid teaching moments. I don't use the ; at the beginning of the cte, but rather as a terminator at the previous clause. Not all cte's require a ;, since many do not follow another batch or statement. If somebody doesn't terminate their statement properly, then teach them and catch the error via code review.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 1813
CirquedeSQLeil (4/21/2010)[hr
I still disagree. This is used as a catch-all to avoid teaching moments. I don't use the ; at the beginning of the cte, but rather as a terminator at the previous clause. Not all cte's require a ;, since many do not follow another batch or statement. If somebody doesn't terminate their statement properly, then teach them and catch the error via code review.

I would really, really love it if I could come to agreement with every developer in our team on the semicolon usage. Unfortunately, the usual response I get is something like "the semicolons are not required yet, so why bother using them". I totally disagree with this logic, but it is prevalent for now.

All I was trying to point out was that from the perspective of the person who actually wrote the recommendation it does make sense considering the facts that unfortunately, many people still don't use semicolons Sad

Oleg
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31918 Visits: 18551
Oleg Netchaev (4/21/2010)
CirquedeSQLeil (4/21/2010)[hr
I still disagree. This is used as a catch-all to avoid teaching moments. I don't use the ; at the beginning of the cte, but rather as a terminator at the previous clause. Not all cte's require a ;, since many do not follow another batch or statement. If somebody doesn't terminate their statement properly, then teach them and catch the error via code review.

I would really, really love it if I could come to agreement with every developer in our team on the semicolon usage. Unfortunately, the usual response I get is something like "the semicolons are not required yet, so why bother using them". I totally disagree with this logic, but it is prevalent for now.

All I was trying to point out was that from the perspective of the person who actually wrote the recommendation it does make sense considering the facts that unfortunately, many people still don't use semicolons Sad

Oleg



Point taken. Make sure you give each of them 15 lashes for disobedience too.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search