none
Best Practice: Use of semi-colon to terminate statements; RRS feed

  • General discussion

  • Is it currently considered best practice to terminate all T-SQL statements with a semicolon? (;)

    Even though I don't think it's part of the ANSI SQL standard, I'm fairly sure I remember reading that it will become required for T-SQL at some point in the future (and, is currently required for statements preceding CTEs etc.).

    So, while in the process of revising and refactoring existing code, would you recommend that developers start future-proofing their code now by slapping a ; on the end of every statement? Should all sample code in Books Online, for example, have semicolons?
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, May 27, 2009 1:55 PM

All replies

  • Hello Tano,

    With CTE introduced in SQL2005, I had began using semicolon in my scripts.

    I do not add ";" to all of them but I think it would be better and bug-free if you have CTE expressions within your code.


    Eralper
    T-SQL Codes


    http://www.kodyaz.com http://www.eralper.com
    Wednesday, May 27, 2009 2:00 PM
    Moderator
  • It's always good to plan for the future, but realistically I can't see Microsoft requiring semicolons without providing a tool to port existing code over.  

    Whereever ambiguity for such a future tool might exist-- such as preceeding a CTE-- it makes sense to require them now, but in other cases, I wouldn't stress overmuch over their presence or absence.
    Michael Asher
    Wednesday, May 27, 2009 2:13 PM
  • Although not needed in the most cases, it makes things more easy like chopping your batch into command without using an external parser. But as it is not enforced I do nout count on it and therefore do not use it. No benefit, extra typing, no enforcements by rules, it probably won´t make it into the public croud :-)

    -Jens

    PS: I will make that thread one week sticky to see how the response is.
    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, May 27, 2009 2:13 PM
    Moderator
  • >(and, is currently required for statements preceding CTEs etc.).

    GO can also be used.

    IMNSHO, i think it is a good practice. Similar to not using SELECT *, it makes it clear what is being done. Further, it makes that much more the transportable for very little cost. Transportability it good in the odd case the RDBMS is switched, and more likely, when a maintenance coder from another RDBMS comes in to this code for the first time.
    Wednesday, May 27, 2009 2:15 PM
    Moderator
  • While the semicolulmn is a engine thing, the GO is only a client statement to indicate when to send the next batch to the server, so no equivalent. There are only a few and simply statement which will make it through a 1:1 conversion to another platform. Normally a translator / migrator (however you call that) is needed to get the code from platform A to platform B, so that shouldn´t be a reasons for the most situations.

    -Jens
    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, May 27, 2009 2:19 PM
    Moderator
  • Good point.

    I meant to respond to the statement that a semi-colon was required. It is not, because GO can be used. However, as you pointed out, that would be not such a good thing in a PROCEDURE.
    Wednesday, May 27, 2009 2:30 PM
    Moderator
  • Yes, you should consider this best practice, but eventually semicolons will be required.  The Database Engine Deprecated Features topic in the 2008 Books Online states that "Not ending Transact-SQL statements with a semicolon" is deprecated and will be discontinued in a future release.  See http://msdn.microsoft.com/en-us/library/ms143729.aspx  for details.

    Regards,
    Gail


    Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights
    Wednesday, May 27, 2009 2:55 PM
  • The use of semi-colon is supported by ANSI and it is a good practice.
    Besides preceding common table expression definition, it is required for
    terminating the MERGE statement.

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Wednesday, May 27, 2009 3:11 PM
    Moderator
  • Yes, you should consider this best practice, but eventually semicolons will be required.  The Database Engine Deprecated Features topic in the 2008 Books Online states that "Not ending Transact-SQL statements with a semicolon" is deprecated and will be discontinued in a future release.  See http://msdn.microsoft.com/en-us/library/ms143729.aspx  for details.

    Regards,
    Gail


    Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights

    Heh... someday, SQL Server will have all of the same problems as Oracle.  ;-)
    --Jeff Moden
    Saturday, May 30, 2009 1:48 AM
  • is a semicolon required on Labels?

    I've seen some examples on the MS site where they have semicolons terminating statement but not on a label.ie...

    some TSQL

    .....

    if @@error > 0 goto someLabel

    ....

    someLabel:


    Shawn Wilson

    Wednesday, July 18, 2018 6:19 PM
  • There are other databases where it will throw error and code will not compile if semi colons are missing. Not all systems are as forgiving as sql server.

    Also don't put too much emphasis on ANSI, major vendors sometimes try to emulate what others are doing and adjust accordingly (ie providing similar features what others are offering) and sometimes ignore ANSI. 





    Wednesday, July 18, 2018 8:23 PM
  • I use semicolons on any new code I write and when I'm modifying old code because I' putting in new features or correcting bugs.  But I do not change old code just to add semicolons.  Even though Microsoft has said some future release might require it at the end of every statement, I think it is very unlikely that will ever happen.  Microsoft, in general, avoids changes that break existing code.

    As others have noted, a semicolon is required for some of the newer features, like before CTE definitions (if it is not the first statement in a batch) or after MERGE statements (if it is not the last statement in the batch).  There are some others like the Service Broker statements SEND and RECEIVE that have semicolon requirements.

    In most cases, if you forget a semicolon that should be there, you get a syntax error and so the problem is easily found and corrected.  There is, however, at least one case where if you forget a semicolon you do not get an error or warning, but the code does not do what you expect.  That can happen when THROW follows a SELECT statement.  For example, even though the following code appears to have a THROW statement that should return the divide by zero error, you will not get an error when running this code.

    begin try;
    select 'Going to divide by zero';
    select 1/0 as 'divide by zero';
    end try
    begin catch;
    select 'But I don''t get an error reported even though I think I have a throw statement'
    throw
    end catch;

    That's because without the semicolon, throw is treated as the column name for the select statement.  So I recommend always preceding THROW with a semicolon even if the previous statement already has a semicolon.  That way, if someone later adds a SELECT without a semicolon (for example, to put in some debugging code), the THROW still does what I want.

    Tom

    Wednesday, July 18, 2018 9:30 PM