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?

Solution 1:

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.

Solution 2:

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.

Solution 3:

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