When should I use semicolons in SQL Server?

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?

**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

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.

From a SQLServerCentral.Com [article][1] 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.


[1]:

[To see links please register here]

In SQL2008 BOL they say that in next releases semicolons will be required. Therefore, always use it.

Reference:

- [Transact-SQL Syntax Conventions (Transact-SQL)][1]
- [Deprecated Database Engine Features in SQL Server 2008 R2][2] ("Features Not Supported in a Future Version of SQL Server" section, "Transact-SQL" area)






[1]:

[To see links please register here]

[2]:

[To see links please register here]

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][1]


[1]:

[To see links please register here]

If I read this correctly, it will be a requirement to use semicolons to end TSQL statements.

[To see links please register here]

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...

[To see links please register here]

EDIT:
I found an even better free tool/plugin called ApexSQL...

[To see links please register here]

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)

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.

*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.