Beginning a CTE

Question

Post reply

Beginning a CTE

Steve Jones - SSC Editor

SSC Guru

Points: 736210
More actions
June 2, 2016 at 8:21 pm

#74540

Comments posted to this topic are about the item Beginning a CTE
June 2, 2016 at 11:14 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 62 total)

You must be logged in to reply to this topic. Login to reply

Terje Hermanseter SSCarpal Tunnel Points: 4851 More actions · Answer 1

Good question. The questions don't have to be long and complex to learn something from them.

Toreador SSChampion Points: 11365 More actions · Answer 2

I knew what was intended but the correct answer surely should be "No, Never".

The semi-colon is not required to begin the CTE; it is required to terminate the previous batch. A pedantic distinction perhaps, but one that will become increasingly important as the terminator becomes required for more and more statements.

Indeed, not ending a statement with a semi-colon is on the deprecated features list. I do not believe they will ever enforce this (it would break too much code), but it is a clear statement of intent.

Better not to start a CTE with a semi-colon, but instead get used to terminating all statements with one.

Louisa Whitton SSCrazy Points: 2577 More actions · Answer 3

Anyone who doesn't terminate SQL statements with a semicolon should note the following (from https://msdn.microsoft.com/en-us/library/ms177563%28v=sql.110%29.aspx ):

Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

It's much easier to start doing this now than have to apply it to all your code at once when it becomes compulsory!

Heals SSCrazy Points: 2234 More actions · Answer 4

Thought the same thing - also Do I always need a semicolon to begin a CTE? Yes, if - therefore no you don't 😛

steve.salt Valued Member Points: 59 More actions · Answer 5

The Question said "Do I always need a semicolon to begin a CTE? "

I picked NO - because the statement begins "Do I Always ...". As a newcomer to SQL, and a pedant, it is a misleading question, because if the previous statement in a batch has not been terminated, you will need to insert the semi-colon, but I would add to the end of the previous statement. The answer says that it is convention , not a hard and fast rule.

Regads,

Steve

paul s-306273 SSChampion Points: 10730 More actions · Answer 6

From my old days of Oracle, I seem to remember it was compulsory in SQL*Plus;

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 7

paul s-306273 (6/3/2016)
From my old days of Oracle, I seem to remember it was compulsory in SQL*Plus;

We've been told for some time (since 2005 I think) that semicolons were going to be enforced within SQL Server. As a rule, I always use them and encourage my fellow teammates to use them as well. I don't understand why MS is wavering on the commitment to enforce them as they've been part of the ISO-1999 standard for a very long time.

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Terje Hermanseter SSCarpal Tunnel Points: 4851 More actions · Answer 8

I don't understand why MS is wavering on the commitment to enforce them as they've been part of the ISO-1999 standard for a very long time.

Too much old code will break.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

The intention was clear enough, but it does terminate a statement. Perhaps it's my background, but I always use them and encourage others to do the same.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 10

Terje Hermanseter (6/3/2016)
I don't understand why MS is wavering on the commitment to enforce them as they've been part of the ISO-1999 standard for a very long time.
Too much old code will break.

That would be true for a number of things due to version upgrades over the years. It's impossible to preserve 100% backward compatibility and yet move forward. If it's a real problem, then MS could offer a tool to insert semicolons where appropriate by using the same parser that is currently used to determine the end of a statement.

Just a thought...

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Terje Hermanseter SSCarpal Tunnel Points: 4851 More actions · Answer 11

I always use them and encourage others to do the same.

I've been too sloppy. I need to be more consistent and start to use them even when I only writes single statements. That way I think I will enforce the habit of always using them.

below86 SSChampion Points: 11532 More actions · Answer 12

martin.whitton (6/3/2016)
Anyone who doesn't terminate SQL statements with a semicolon should note the following (from https://msdn.microsoft.com/en-us/library/ms177563%28v=sql.110%29.aspx ):
Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.
It's much easier to start doing this now than have to apply it to all your code at once when it becomes compulsory!

+100

The requirement for using a semicolon at the end of each statement should have been there from the beginning.

To me it would be like writing a COBOL statement and forgetting the period.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 13

Toreador (6/3/2016)
I knew what was intended but the correct answer surely should be "No, Never".
The semi-colon is not required to begin the CTE; it is required to terminate the previous batch. A pedantic distinction perhaps, but one that will become increasingly important as the terminator becomes required for more and more statements.

Well said. +1