';' at the beginning of TSQL statements

Question

From time to time I see SQL Server statements that start with semicolon ';' like below

;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1

The other example is ;THROW

Why exactly there is a ';' the beginning of TSQL statements

Update 1:

Please note that I am asking about ';' at the beginning of statements. This question is not duplicate of this one

When should I use semicolons in SQL Server?

Update 2:

@MartinSmith answer makes sense.

Just to make sure we have a complete answer for this post, consider this respected article:

http://www.sommarskog.se/error_handling/Part1.html#jumpTHROW

At this point you might be saying to yourself: he must be pulling my legs, did Microsoft really call the command ;THROW? Isn't it just THROW? True, if you look it up in Books Online, there is no leading semicolon. But the semicolon must be there. Officially, it is a terminator for the previous statement, but it is optional, and far from everyone uses semicolons to terminate their T‑SQL statements.

I agree with @MartinSmith answer, but it seems that the matter is being taken to some quite extreme levels.

Typically, in a stored procedure THROW is a statement by its own line. SQL developers simply don't just merge SQL lines like that and miss a semicolon.

To me, there is more chance people accidently drop a table than mixing 'THROW' statement with another line of TSQL

Is the case explained in the quote above something extreme and rare to happen? or I am missing a point here?

score 13 · Accepted Answer · edited May 23 '17 at 12:32

It is supposed to be after the statements not before them. But in most cases in TSQL the terminating semi colons on statements are currently optional in practice (though technically Not ending Transact-SQL statements with a semicolon is deprecated) and the presence of statement terminating semi colons is not enforced.

An exception is the MERGE statement (that does require a terminating semi colon) and also statements preceding WITH or THROW

So this is a somewhat defensive practice for answers on StackOverflow in case the OP (or future readers) pastes it into the middle of some existing batch that doesn't have the required semi colon on the preceding statement and then complains it doesn't work and they receive the following error.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

In the case that the preceding statement is terminated with a semicolon the additional one does no harm. It just is treated as an empty statement.

This practice can itself cause problems though where the CTE is used in a context where multi statements are not valid. e.g. Inserting a semicolon before the WITH here would break it.

CREATE VIEW V1
AS
  WITH T(X)
       AS (SELECT 1)
  SELECT *
  FROM   T;

Similarly for THROW blindly inserting a leading semi colon can cause problems too.

IF @i IS NULL
;THROW 50000, '@i IS NULL', 1;

Incorrect syntax near ';'.

I have fixed the example you give in your question and changed it to

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

Thank you for help here. Would you be able to share your though on my updated comments? — Allan Xu, Aug 07 '16 at 22:33
@AllanXu - Yes I agree with your sentiments on `;THROW` - This practice is only something I would consider for code samples on the web. Not something in my own source code. As the correct thing to do is just to make sure you terminate the preceding statements (and ideally all statements) with semi colons. — Martin Smith, Aug 08 '16 at 10:57

score 2 · Answer 2 · answered Aug 08 '16 at 13:33

It think this is about giving the parser a fighting chance to figure out what the code is supposed to do.

throw is not a reserved keyword and with can be used as part of other statements.

Perfectly valid statement in Transact-SQL:

create table throw
(
  ID int
) 
with(data_compression = none)

Note that line breaks is not something the parser uses to separate one statement from another.

create 
table 
throw(ID int) 
with (data_compression = none) insert into 
throw values(1) select 
* from throw

It uses some other kind of magic to figure out that you really meant this.

create table throw(ID int) 
with (data_compression = none);

insert into throw values(1);

select * 
from throw;

So, to make things easier when CTE's was introduced in SQL Server 2005 and when throw was added in SQL Server 2012 it is required that the preceding statement is terminated with a statement terminator.

Same reason for requiring a ; at the end of a merge statement.

declare @T table(ID int, Value int);

merge into @T as T
using (values(1, 100)) as S(ID, Value)
on T.ID = S.ID
when matched then 
  update set Value = S.Value
when not matched then
  insert (ID, Value) values(S.ID, S.Value);

Easier to parse the code and see where the statement actually ends.

';' at the beginning of TSQL statements

2 Answers2