Increasingly I see other people's code using ";" to terminal some/most/all statements inside an Sproc. Why?
With the exception of
; WITH MyCTE
AS
...
I never use ";". Its an annoyance if I want to add another AND to the end, or another line for an additional ORDER BY column, or whatever.
It also mucks up the Control-Right-Arrow "word hopping" behaviour in my editor.
But perhaps I'm missing something ... does the Query Planner split the Sproc into "chunks" according to each defined statement?
or is the parser faster because it doesn't have to read-ahead before it can determine where an individual statement starts / ends?
or is it a more "defensive coding" technique because there is no chance of a statement accidentally being parsed in-with the following statement?
Or something else?
Do you use ";" after every statement in an Sproc? and if so why? (e.g a description of why your Coding Standards decided that that suited our company )
The semi-colon before the WITH keyword when starting a CTE is just defensive programming. WITH keyword can be used in multiple contexts (WITH CHECK for creating constraints, or for creating views etc., as well as for starting a CTE). The way SQL Server determines that you are starting a CTE is by testing whether the WITH keyword is preceded by a semi-colon (i.e., termination of the previous statement) unless it is the first thing in a batch.
So the following will not parse correctly.
SELECT TOP 10 col1 FROM myTable
WITH cte AS
(
SELECT TOP 5 x from yourtable
)
SELECT SUM(x) FROM cte
Preceding the WITH with a semi-colon fixes that problem regardless of whether the previous statement was terminated with a semi-colon or not.
Btw, I always try to use semi-colons to end a statement - probably because of similarity with C#.
A ; was always allowed as a statement terminator, MS just didn't require it. MS is stating now that they may require ;s at the end of statements in the future. Therefore, many people are adding them now so that if they do become required, they will already be in place in their code.
Thanks. MY guess is that terminating each statement would make parsing faster (perhaps not now, but if they became "required" the parser could, perhaps?, be changed to more quickly recognise that a statement was in error)
Also means that further additions to the language could be made without having to worry about whether they could be ambiguous in conjunction with a preceding / following statement ..
but ... never having had to have them it does seem to me to be a not-insignificant pain working with code that contains them
)