semicolon terminator

semicolon terminator

• fmoreau31

  >>why is this considered a statement that requires termination
  >> Use dbo.MyDatabase;

  >>

   

  Why do you say that this statement requires termination Statement terminators are completely optional in TSQL. You can however use statement terminators and it is recommended now since a future version of SQL Server might require it by default. Having said this, there are some new keywords whose usage can be ambiguous if there are no statement terminators and hence they require those to resolve the ambiguity.

  And this is just the nature of SQL as a language. For example, there is a WITH clause for specifying hints and another for CTE. If you use statement terminators then it is not an issue since every statement will have it and you will be fine. So there is really no confusion, the issue is with code that doesn't use statement terminators and expects the parser to resolve the ambiguities which it can't in all cases and due to optional clauses/keywords.

   

  Consider example below:

   

  select * from X

  sp_helpdb

   

  Now this might look like two statements and that is what the user might expect but without statement terminator sp_helpdb will be considered as table alias. The parser cannot magically resolve the ambiguity in this case and since AS keyword is optional for table alias "sp_helpdb" is interpreted as table alias. Now, if you had statement terminators for these two statements you will get an error since the literal sp_helpdb by itself as a statement doesn't mean anything to the parser. On the other hand a statement "sp_helpdb;" will work fine. And here is where the difference between ad-hoc statement and statements in a batch come into play. This is rather unique to the SQL language and their procedural counterparts. For ad-hoc statements, only that particular statement is compiled and some of the keywords are completely optional whereas the same statmeent used in a batch is different. Note that the ANSI SQL standard also specifies statement terminator and the various type of statements is beyond the scope of the post. You can take a look at the standard docs for details. The optional statement terminator is something we inherited from Sybase days. Oracle/DB2 for example require statement terminators.

   

  So the bottomline is that if you don't rely on use of optional keywords, statement terminators and clauses you are fine. Otherwise you will be in for some nasty surprises where even incorrect code will run just fine. Hope this helps.

   

  Also, the article that you mentioned is wrong about few things. For instance, GO is not a SQL or TSQL command. It is just a batch separator for the client, the server has no knowledge of the statement and it will throw an error actually unless you have a SP itself called go which you can execute using dynamic SQL or call from client API directly. Lastly, you can configure GO to be ~ or ^^ or whatever you wish. And the rules for batch are pretty clear and documented in Books Online so I don't have to go into those details.

  
  
  

• aetor

  ";" is going to be crucial as C# can be used to write procedures and other T-SQL functionality.

  If you are looking for difference between using "GO" and ";" the article you found after google search is enough to tell you everything you need to know.

  
  

• BSautner

  Thanks to all of you guys. I finally have a better grasp.

  1. Blair, yes I read the article. It describes two anecdotes of usage, not a definition.

  2. Chad, yes I found those links in BOL. They are also just anecdotes of usage.

  A definition, where I come from, is a document titled 'Statement Terminator' and such does not exist! So I stand by my original position that it is undocumented. Piecing together tidbits and hunting inside docs of structures as yet unfamiliar to me just to make sure I exhausted all possible mentions is real unproductive, especially if you multiply the likes of me by hundreds of thousands.

  3. Umachandar, thanks for clearing up the additional instances where the semicolon appears. They are actually quite a few. As newbies we learn from code in articles etc, but most of all from right-click scripting of existing objects. This now appears all over the place and it was hard to tell what it meant until I cried wolf in the forum.

  Thanks again for all your help. 2 out of the three of you seem to be MS emplyoees, so I hope you forward this as a suggestion to include it in the docs. It is not insignificant and by the looks of it it will be used even more in the future.

  Carl

  
  

• Bronco Billy

  You should use the send feedback button in Books Online and post your comments. This is actually better since it will create a bug automatically and get triaged. This is beneficial in many ways. I will also send email about this internally to some of the document writers. I agree that the parsing rules for TSQL can be quite confusing and difficult to understand for new programmers.
  
  

• Chipe

  The Semicolon The semicolon character is a statement terminator. It is a part of the ANSI SQL-92 standard, but was never used within Transact-SQL. Indeed, it was possible to code T-SQL for years without ever encountering a semicolon. Usage There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch. The second is where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.

  CTE: http://msdn2.microsoft.com/en-us/library(d=robot)/ms190766.aspx

  service broker: http://msdn2.microsoft.com/en-us/library(d=robot)/ms171620.aspx

  
  
  

• shyamu

  Hi Chad Boyd,
  
  This is Bhanu Prakash and i am going to be the new administrator for MSDN Forums. You are amongst top 200 answerers in Forums and we identified you as potential moderator.Please reply back to my email (a-bhanun@microsoft.com) with your email address so that we can give you moderator rights.

  We appreciate your continued support for the Forums. Congratulations once again!!
  
  Thank you,
  Bhanu Prakash
  MSDN Forums Admin

  
  
  

• Thomas Mack

  Thanks

  Done!

  Carl

  
  

• unique_username

  Hi Carl.

  Just for clarification, the ; statement teminator isn't new to SQL 2005...it's part of the SQL-92 standard, and has been an optional statement terminator for years within versions of SQL Server, including 7 and 2000.

  As for it being documented in BOL, it's stated in a couple of places as the optional statement terminator in SQL 2005 BOL, see the following link for example:

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/35fbcf7f-8b55-46cd-a957-9b8c7b311241.htm

  And, it's also used in many examples throughout BOL.

  As for when it's required, it will be clearly stated in BOL when a statement terminator is required for use, a couple of the primary places it's required now is with CTE's, XML, and Broker functionality. See the following links in BOL to see that it is documented within each of these as being required and where:

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/878c6c14-37ab-4b87-9854-7f8f42bac7dd.htm

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/4da8a855-33c0-43b2-a49d-527487cb3b5c.htm

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm

  The reason it hasn't been pushed as being required in this release for everything is due to backward-compatibility issues (amoung other things)...obviously, we couldn't require all t-sql statements in SQL 2005 to be terminated, as it would require massive re-writes of many many lines of code to port existing 2000 databases to 2005.

  Also, to address the point on use of C# and other .NET code within the SQL engine in 2005, note that all this code runs within a managed CLR environment that is simply hosted by the SQL 2005 process, so CLR integration really has nothing to do with why the statement terminator is required for some T-SQL statements now and not for others...CLR code is not considered T-SQL syntax, it runs within a CLR environment in the SQL process space. So, it's not like you'll ever have stored procedures that have a mix of C# and T-SQL code side-by-side.

  And finally, the terminator is now required only by some new 2005 functionality, not required for anything that is backward compatible. Though this is the case, I'd strongly recommend everyone get accustomed to using it to terminate all statements, as it may become required in the future.

  HTH

  
  
  

• sabor

  Hi Blair,

  Obviously the concept of statement is different from that in c# and that is what is confusing.

  For example, javascript also does not require(it should but it does not) statement terminator so you don't have to use it but the concept of a statement is IDENTICAL to c#. I like to terminate all my js code, especially since before deployment I take out the linebreaks to reduce the code size.

  in T-SQL we had both discrepancies: different concept of statement and different level of enforcing the terminators.

  Example:
  why is this considered a statement that requires termination
  USE dbo.MyDatabase;

  Now the semicolon in some situations becomes a must and in others it is not required.

  It boils down to what is a statement
  Is a variable assignment a statement
  Is the end of a block a statement

  If a new concept is being introduced why isn't it officially documented  That article is not MSDN or BOL. It is good that the author took the time to spotlight this topic and no one else has. There are some forum posts but no other articles. It is by far not exhaustive and the examples not for newbies.

  Termination however is something that newbies have to deal with, so it is fundamental. You guys that have been writing code may have the right intuition already developed but newbies don't have intuition to rely on yet.

  
  

• Akinja

  actually I think that is a very good article outlining the usage of GO and statement terminators.

  GO separates Script blocks

  ; defines the end of a statement within a script block.

  what is unclear

  
  
  

• oklima

  I'll also add that the example you gave above as requiring a statement terminator (use <dbname> go) definately does not require a statement terminator...the only time it may is if what follows it is a CTE for example, or possibly a service broker statement.

  You can definately execute a 'use <db>' statement in 2005 without a statement terminator...

  
  
  
• semicolon terminator