When should I use semicolons in SQL Server?

Question

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.

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.

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.

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)

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/

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