Lynn Pettis (6/4/2015)
Jonathan AC Roberts (6/4/2015)
The reason a semicolon is required before the WITH in a CTE as that WITH has two uses, it can be used for hints e.g.
SELECT * FROM MyTable WITH (NOLOCK)and used in a CTE:
WITH CTE AS(SELECT * FROM...With a single pass compiler you need to be able to tell if the WITH is part of the SELECT statement or the start of a CTE or a syntax error.
When using WITH to define a CTE the PRECEDING statement MUST be terminated with a semicolon. The semicolon does NOT belong at the beginning of the CTE declaration.
Preceding the WITH with a semicolon is nothing more than a crutch to ensure that the preceding statement is terminated with a semicolon.
Yes, of course you are correct, the official term for a semicolon is a "statement terminator". You can use a CTE if it is the first line in a block without a semicolon, e.g.
GO
WITH CTE AS (SELECT * FROM myTable...
but whether you word it as "a semicolon before the CTE" or "a semicolon after the preceding statement" is just tautology. The semicolon is a statement separator just as a GO is a batch block separator or batch block terminator.
But the reason Microsoft make you terminate the preceding statement with a semicolon is because of the double usage of WITH and compilation. Personally, I dislike computer languages that use punctuation.