Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Views Expand / Collapse
Author
Message
Posted Wednesday, April 21, 2010 7:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 3,388, Visits: 2,019
Oops, I glossed over that before. Thanks Hugo!
Post #907725
Posted Wednesday, April 21, 2010 8:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 1,142, Visits: 2,691
Good straight forward question! Thanks!

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Post #907798
Posted Wednesday, April 21, 2010 9:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:55 AM
Points: 925, Visits: 1,734
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.
Post #907806
Posted Wednesday, April 21, 2010 9:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 1,676, Visits: 1,760
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
Post #907853
Posted Wednesday, April 21, 2010 9:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 17,964, Visits: 15,967
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
Post #907869
Posted Wednesday, April 21, 2010 11:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #907969
Posted Wednesday, April 21, 2010 11:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 1,676, Visits: 1,760
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
Post #907978
Posted Wednesday, April 21, 2010 11:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 17,964, Visits: 15,967
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
Post #907987
Posted Wednesday, April 21, 2010 11:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 1,676, Visits: 1,760
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

Oleg
Post #907997
Posted Wednesday, April 21, 2010 12:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:31 PM
Points: 17,964, Visits: 15,967
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

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
Post #908012
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse