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

[Addition 2011103: 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:

1.       It is standard

2.       More and more specific cases make it a requirement to avoid ambiguity as demonstrated

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

4.       It makes your code cleaner, more readable and easier to maintain

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

Cheers,

BG