#0235 – SQL Server – T-SQL Deprecated feature – Start using semi-colons as statement-terminators


A while ago, I read a post from Aaron Bertrand (B|T) regarding an appeal to start using semi-colons actively as statement terminators. Semi-colons as statement-terminators has been around for as long as I can remember, however, they have only been made mandatory in the newer statements:

  • A semi-colon is required before the WITH clause (as in Common Table Expressions, CTE)
  • The MERGE statement must end with a semi-colon
  • In SQL 2012, the THROW statement also requires that the preceding statement ends with a semi-colon

Because the semi-colon is not mandatory, most developers do not use semi-colons in the T-SQL queries that they write. However as Aaron points out, it is already documented in Books On Line that the semi-colon will be a required feature:

Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

For most teams, this would mean to modify almost every line of code and a huge testing & development effort. To mitigate the high development effort and bring reliability in the process, teams may decide to write a small program that would add semi-colons after each statement. But there’s a small catch.

Exception to the rule: The batch separator cannot be suffixed by a semi-colon.

USE AdventureWorks2012;
GO;

The code above would simply fail to compile with the following error.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘GO’.

The reason is quite simple – a T-SQL statement is a sub-set of a batch. Because “GO” is a batch separator, it cannot be suffixed by a statement terminator because an active T-SQL batch would not exist at that time.

Reference:

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

4 thoughts on “#0235 – SQL Server – T-SQL Deprecated feature – Start using semi-colons as statement-terminators

  1. dishdy

    It would be very difficult for human beings to read books without that wonderful point at the end of a sentence. In fact, it would be difficult even without commas. And sometimes I would not mind at all seing that semi-colon in the sql code I read.

    I wonder how Microsoft came up with the idea of not requiring you to place a semi-colon at the end of a statement. Anyone coding in Java will have no objections. And the code required in the engine to parse sql becomes much more complex. So Microsoft has decided enough is enough…

    Like

    Reply
  2. balakrishna141

    Hi Nakul,

    Nice One.I will also start writing code with semi-colon where ever necessary

    thanks for sharing original post.

    Like

    Reply
  3. Nakul Vachhrajani

    @dishdy: Very true! I agree – statement termination should be mandatory.

    @Bala Krishna: I am glad you found the post useful.

    @Madhivanan: That post is indeed fun with GO! Very interesting. Thanks for sharing.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s