Why CTE should begin with semicolon or Go

Question

Why CTE should begin with semicolon or Go

er.mayankshukla

SSCarpal Tunnel

Points: 4190
More actions
October 7, 2014 at 7:16 am

#312039

I had this doubt that why CTE should begin with semicolon or Go.
My thinking is , that this is for the same reason as creating stored procs or functions where the sql engine treats them as a batch.
Ref. http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx
Need your inputs on this.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The requirement is that it be appropriately separated from previous SQL statements, not that is HAS to have a ; or Go before it. If your CTE is the first thing in your SQL statement, you don't need any form of punctuation etc.... As one of the regulars heres likes to point out, the semicolon is a statement TERMINATOR not a starter, so it's more appropriate to put is at the end of the previous statement than at the beginning of the current one.

As best as I can tell - the main reason the CTE syntax is strong about making sure it is separated from previous statement is becuase WITH is a keyword that is used in several contexts (locking hints, queryhints etc...). The separation makes it clear to the parser which WITH usage we're going for.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

The WITH keyword can also be used in other parts of the TSQL language.

So the parser has to know if WITH is the first word in the batch. If it is, it is the start of a CTE.

If WITH is preceded by GO or by a semicolon, the parser will know it is the first word and that it is a CTE.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

er.mayankshukla (10/7/2014)
I had this doubt that why CTE should begin with semicolon or Go.

It shouldn't.

If the CTE is not the first statement in the batch, then the previous statement needs to be properly terminated with a statement terminator - ;

Same reason why MERGE needs to be terminated with a ;, why THROW requires that the previous statement be terminated with a ;, because it makes it easier to write a parser, less likely that the parser will get confused or wrong.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 4

I'll also add that you should start changing any code you touch for maintenance or any new code to include semi-colons for ALL statements that can take them because MS has stated that NOT having semi-colons has been deprecated and some version in the future will require them everywhere that they can be placed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Amen. A semicolon is also a terminator, so it should END a statement, not start one.

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

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 6

Sorry for the confusion . My question was why the previous statement should be terminated before starting cte . my bad , I wrote it wrong .

Well I got the solution.

Thanks so much , all you geniuses