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?
From a SQLServerCentral.Com article by Ken Powers:
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.
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.
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.
You must use it in some cases.
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
In SQL2008 BOL they say that in next releases semicolons will be required. Therefore, always use it.
- 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)
If I read this correctly, it will be a requirement to use semicolons to end TSQL statements.
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…
I found an even better free tool/plugin called ApexSQL…
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';
Semicolons are optional Semicolons are optional Semicolons are optional Semicolons are optional
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.
outside a BEGIN TRY/END TRY block worked fine to commit the transaction, but inside the block it had to be
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.
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)
It appears that semicolons should not be used in conjunction with cursor operations:
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:
and this gave me error 16916.
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.
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.
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 <= 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.
Semicolons do not always work in compound SELECT statements.
Compare these two different versions of a trivial compound SELECT statement.
DECLARE @Test varchar(35); SELECT @Test= (SELECT (SELECT (SELECT 'Semicolons do not always work fine.';););); SELECT @Test Test;
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
Test ----------------------------------- Semicolons do not always work fine. (1 row(s) affected)