Semicolon

While reviewing the list of deprecated features in SQL Server Denali’s books online, my friend and colleague Herbert Albert discovered a small thing that will have important implications on anyone who’s writing T-SQL code. Under the article: Deprecated Database Engine Features in SQL Server "Denali," section: Features Not Supported in a Future Version of SQL Server, you will now find the following deprecated feature: Not ending Transact-SQL statements with a semicolon.

Related: The New Kid in Town: SQL Server Denali

The Replacement is (unsurprisingly): End Transact-SQL statements with a semicolon (;). Deprecated features that appear in this section will still be supported in the next version of SQL Server, but will be removed in a later version. This could mean two versions down the road or later.

Note: Addition 2011103 is courtesy of Sean McCown and Aaron Bertrand, this deprecated feature already appeared in the documentation starting with SQL Server 2008.

Already in SQL Server 2005 and 2008 there are cases where it is a requirement to use a semicolon, including:

  • Terminating the statement prior to a WITH clause defining a CTE
  • Terminating the statement prior to a SEND or RECEIVE service broker statement
  • Terminating the MERGE statement

These cases make the use of a semicolon a requirement to avoid ambiguity in the meaning of certain keywords. For example, the WITH keyword is used in the language for a number of different purposes. When the T-SQL parser analyzes the WITH token it needs to know whether the token starts a new statement defining a CTE or belongs to the previous statement, e.g., defining a table hint in a query. Many T-SQL developers got to the habit of starting a new CTE definition like this:

;WITH …

To me this form seams unnatural and nonintuitive, and such properties are not desirable properties for your code.

Also the fact that so far it wasn’t a requirement to terminate all statements with a semicolon makes it hard on Microsoft when considering new language features purely due to parsing complexities. For example, consider the new OFFSET-FETCH filtering clause in SQL Server Denali which I covered in detail both in a past blog entry and in my column. In standard SQL the OFFSET clause is optional when you specify a FETCH clause; the default is supposed to be OFFSET 0 ROWS. However, in T-SQL if you specify the FETCH clause, the OFFSET clauses is mandatory. The reason is that otherwise the parser can’t tell whether the FETCH clause starts a new statement that fetches the next record from a cursor, or is the new filtering clause belonging to the previous statement.

So far it was a requirement to use a semicolon only in specific cases. Now it looks like the plan is to make it a required terminator for all* T-SQL statements in some future version of SQL Server.

* Naturally there are cases that aren’t supposed to be terminated with a semicolon; those include (but are not limited to):

- BEGIN

- BEGIN TRAN

- IF

- ELSE

- WHILE

- BEGIN TRY

- END TRY

- BEGIN CATCH

It remains to be seen in which version exactly Microsoft will decide to enforce this. My guess is that the decision will probably be influenced by how customers and the SQL Server community will react to this plan. Trying to guess how much T-SQL code is out there in production systems, probably many millions of lines of code if not billions. Obviously from a practical perspective it’s not a simple feat for customers to achieve, and very likely there will be some resistance. However, in the long run it is only logical to make it a requirement to terminate all statements with a semicolon for a number of reasons:

  • It is standard
  • More and more specific cases make it a requirement to avoid ambiguity as demonstrated
  • The fact that currently it’s not a requirement across the board for all statements causes complexities for the parser and likely prevents additions of some new language features
  • It makes your code cleaner, more readable and easier to maintain
  • Your code is more portable

So now is a good time to get to the habit of terminating all of your statements with a semicolon, and making it a policy among all developers. Once you get to the habit, it becomes effortless. Then it will be more realistic to accept such a requirement in a future version of SQL Server and help Microsoft and ourselves achieve this important goal.

Discuss this Blog Entry 3

on Oct 3, 2011
Thanks to Sean McCown and Aaron Bertrand for pointing out that this already appears as a deprecated feature in the documentation for SQL Server 2008 and later.
on Oct 3, 2011
Thanks to Sean McCown and Aaron Bertrand for pointing out that this already appears as a deprecated feature in the documentation for SQL Server 2008 and later.
on Sep 30, 2012
I would argue that most of the exceptions you call out are merely clauses, not statements in themselves. The terminating semicolon would still come after the complete statement of which the clause is a part. Why do you say that the BEGIN TRAN statement should not be terminated by a semicolon? MSDN gives the following syntax for SQL Server 2012's BEGIN TRANSACTION statement: BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ] ] [ ; ] This suggests that a terminating semicolon is unnecessary but acceptable. All the code examples in the statement's documentation use the terminating semicolon consistently: Marked transactions BEGIN TRAN T1; UPDATE table1 ...; BEGIN TRAN M2 WITH MARK; A. Naming a transaction BEGIN TRANSACTION @TranName; B. Marking a transaction BEGIN TRANSACTION CandidateDelete WITH MARK N'Deleting a Job Candidate';

Please or Register to post comments.

What's Puzzled By T-SQL Blog?

T-SQL tips and logical puzzles from Itzik Ben-Gan.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×