T-SQL 101: 12 Using Statement Terminators in T-SQL

I think T-SQL is an odd language in many ways. One aspect of this is the looseness of the language. The best example of this is the way that statement terminators (i.e. the semicolons at the end of the statements) are optional.

Back in 2005, the optionality of the statement terminators was deprecated. The SQL Server team told us to start using them, because one day they’ll be required.

Given the amount of existing code that doesn’t have them, I’d rate the chance of them ever enforcing that change as pretty low. However, I do think that having consistent use of statement terminators is a good thing.

Part of the reason for that change in 2005 was that they were introducing statements at the time, that needed to be separated from the previous statement. In particular, WITH (used with common table expressions or CTEs), and SEND and RECEIVE (both used with Service Broker), required separation from previous statements.

I’ve seen many people who aren’t used to using semicolons, start to write these statements like this:

;WITH

;SEND

;RECEIVE

That way, they didn’t care whether the previous statement was terminated or not. I can see why they do it, but I’d rather just have terminators all the time, on every statement.

The one place where I really struggle with them though is BEGIN and END. I think of a pair of these as a single statement:

BEGIN

…

END;

However, it turns out that you can write this:

BEGIN;

…

END;

To me, that looks seriously odd, so that’s the one time that I don’t do it. I think that’s a very confusing usage.

Otherwise, I use them all the time, and I think you should too, particularly if you’re just learning.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-04-08