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?

Answer 1

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.

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

Answer 3

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

Answer 4

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

Answer 5

Here is a full article dedicated to this issue on sqlservercentral.

The GO Command and the Semicolon Terminator

Answer 6

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.