When should I use semicolons in SQL Server

Question

While checking some code on the web and scripts generated by SQL Server Management Studio I have noticed that some statements are ended with a semicolon    So when should I use it

User · Answer

Semicolons do not always work in compound SELECT statements.

Compare these two different versions of a trivial compound SELECT statement.

The code

DECLARE @Test varchar(35); 
SELECT @Test=
    (SELECT 
        (SELECT 
            (SELECT 'Semicolons do not always work fine.';););); 
SELECT @Test Test;

returns

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.

However, the code

DECLARE @Test varchar(35)
SELECT @Test=
    (SELECT 
        (SELECT 
            (SELECT 'Semicolons do not always work fine.'))) 
SELECT @Test Test

returns

Test
-----------------------------------
Semicolons do not always work fine.

(1 row(s) affected)

User · Answer

When using either a DISABLE or ENABLE TRIGGER statement in a batch that has other statements in it  the statement just before it must end with a semicolon   Otherwise  you ll get a syntax error   I tore my hair out with this one     And afterwards  I stumbled on this MS Connect item about the same thing   It is closed as won t fix    see here

User · Answer

From a SQLServerCentral Com article by Ken Powers   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

User · Answer

In SQL2008 BOL they say that in next releases semicolons will be required  Therefore  always use it   Reference    Transact-SQL Syntax Conventions  Transact-SQL  Deprecated Database Engine Features in SQL Server 2008 R2   Features Not Supported in a Future Version of SQL Server  section   Transact-SQL  area

User · Answer

If you like getting random Command Timeout errors in SQLServer then leave off the semi-colon at the end of your CommandText strings   I don t know if this is documented anywhere or if it is a bug  but it does happen and I have learnt this from bitter experience    I have verifiable and reproducible examples using SQLServer 2008   aka -  In practice  always include the terminator even if you re just sending one statement to the database

User · Answer

Personal opinion  Use them only where they are required   See TheTXI s answer above for the required list    Since the compiler doesn t require them  you can put them all over  but why  The compiler won t tell you where you forgot one  so you ll end up with inconsistent use    This opinion is specific to SQL Server  Other databases may have more-stringent requirements  If you re writing SQL to run on multiple databases  your requirements may vary    tpdi stated above   in a script  as you re sending more than one statement  you need it   That s actually not correct  You don t need them   PRINT  Semicolons are optional  PRINT  Semicolons are optional  PRINT  Semicolons are optional   PRINT  Semicolons are optional     Output   Semicolons are optional Semicolons are optional Semicolons are optional Semicolons are optional

User · Answer

I still have a lot to learn about T-SQL  but in working up some code for a transaction  and basing code on examples from stackoverflow and other sites  I found a case where it seems a semicolon is required and if it is missing  the statement does not seem to execute at all and no error is raised   This doesn t seem to be covered in any of the above answers    This was using MS SQL Server 2012    Once I had the transaction working the way I wanted  I decided to put a try-catch around it so if there are any errors it gets rolled back   Only after doing this  the transaction was not committed  SSMS confirms this when trying to close the window with a nice message alerting you to the fact that there is an uncommitted transaction   So this   COMMIT TRANSACTION    outside a BEGIN TRY END TRY block worked fine to commit the transaction  but inside the block it had to be   COMMIT TRANSACTION    Note there is no error or warning provided and no indication that the transaction is still uncommitted until attempting to close the query tab   Fortunately this causes such a huge problem that it is immediately obvious that there is a problem   Unfortunately since no error  syntax or otherwise  is reported it was not immediately obvious what the problem was   Contrary-wise  ROLLBACK TRANSACTION seems to work equally well in the BEGIN CATCH block with or without a semicolon     There may be some logic to this but it feels arbitrary and Alice-in-Wonderland-ish

User · Answer

You must use it      The practice of using a semicolon to terminate statements is standard and in fact is a requirement       in several other database platforms  SQL Server requires the semicolon only in particular       cases   but in cases where a semicolon is not required  using one doesn   t cause problems        I strongly recommend that you adopt the practice of terminating all statements with a semicolon        Not only will doing this improve the readability of your code  but in some cases it can       save you some grief   When a semicolon is required and is not specified  the error message SQL       Server produces is not always very clear     And most important      The SQL Server documentation indicates that not terminating T-SQL statements with       a semicolon is a deprecated feature  This means that the long-term goal is to enforce use       of the semicolon in a future version of the product  That   s one more reason to get into the       habit of terminating all of your statements  even where it   s currently not required    Source  Microsoft SQL Server 2012 T-SQL Fundamentals  by Itzik Ben-Gan     An example of why you always must use   are the following two queries  copied from this post    BEGIN TRY     BEGIN TRAN     SELECT 1 0 AS CauseAnException     COMMIT END TRY BEGIN CATCH     SELECT ERROR MESSAGE       THROW END CATCH     BEGIN TRY     BEGIN TRAN     SELECT 1 0 AS CauseAnException      COMMIT END TRY BEGIN CATCH     SELECT ERROR MESSAGE        THROW END CATCH

User · Answer

Note  This answers the question as written  but not the problem as stated  Adding it here  since people will be searching for it  Semicolon is also used before WITH in recursive CTE statements    WITH Numbers AS       SELECT n   1     UNION ALL     SELECT n   1     FROM Numbers     WHERE n 1  lt   10   SELECT n FROM Numbers   This query will generate a CTE called Numbers that consists of integers  1  10   It is done by creating a table with the value 1 only  and then recursing until you reach 10

User · Answer

By default  SQL statements are terminated with semicolons  You  use a semicolon to terminate statements unless you ve  rarely  set a new statement terminator   If you re sending just one statement  technically you can dispense with the statement terminator  in a script  as you re sending more than one statement  you need it   In practice  always include the terminator even if you re just sending one statement to the database   Edit  in response to those saying statement terminators are not required by  particular RDBMS   while that may be true  they re required by the ANSI SQL Standard  In all programming  if we can adhere to a Standard without loss of functionality  we should  because then neither our code or our habits are tied to one proprietary vendor   With some C compilers  it s possible to have main return void  even though the Standard requires main to return int  But doing so makes our code  and ourselves  less portable    The biggest difficulty in programming effectively isn t learning new things  it s unlearning bad habits  To the extent that we can avoid acquiring bad habits in the first place  it s a win for us  for our code  and for anyone reading or using our code

User · Answer

If I read this correctly  it will be a requirement to use semicolons to end TSQL statements  http   msdn microsoft com en-us library ms143729 28v sql 120 29 aspx  EDIT  I found a plug-in for SSMS 2008R2 that will format your script and add the semicolons  I think it is still in beta though     http   www tsqltidy com tsqltidySSMSAddin aspx  EDIT  I found an even better free tool plugin called ApexSQL    http   www apexsql com

User · Answer

It appears that semicolons should not be used in conjunction with cursor operations  OPEN  FETCH  CLOSE and DEALLOCATE  I just wasted a couple of hours with this  I had a close look at the BOL and noticed that     is not shown in the syntax for these cursor statements    So I had   OPEN mycursor    and this gave me error 16916   But   OPEN mycursor   worked

User · Answer

According to Transact-SQL Syntax Conventions  Transact-SQL   MSDN      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     also see  gerryLowry  s comment

[sql-server] When should I use semicolons in SQL Server?

Examples related to sql-server

Examples related to tsql