Why should a CTE start with a semi-colon?

I always do it when posting here or on StackOverflow because for WITH - since the keyword is overloaded - the previous command requires a terminating semi-colon. If I paste a code sample that uses a CTE, inevitably some user will paste it into their existing code, and the previous statement won't have the semi-colon. So the code breaks, and I get complaints like:

Your code broke! I got this error message:

Incorrect syntax near 'WITH'...

While I'd like to believe that folks are becoming better about always terminating their statements with a semi-colon, I'd rather pre-empt the noise and just always include it. Some people don't like it, but <shrug />. You can include as many semi-colons before or after a valid statement as you want. This is valid:

;;;;SELECT 1;;;;;;;;;;;;SELECT 2;;;;;;;;SELECT 3;;;;;

So there is no harm in there being an extra semi-colon preceding a statement that by definition requires it. It is safer to do so even if it ain't so pretty.

It has to be worded weirdly to get the point across, but "not ending a valid statement with a semi-colon" is actually deprecated since SQL Server 2008. So as I describe in the blog post I link to above, even in cases where it's not required to bypass an error, it should be used wherever valid. You can see this here (and this entry has been there for multiple versions):

• Deprecated Database Engine Features in SQL Server 2016
  (search this page for semicolon)

Of course it wouldn't be SQL Server if there weren't exceptions. Try this:

BEGIN TRY;
  SELECT 1/1;
END TRY;
BEGIN CATCH;
  SELECT 1/1;
END CATCH;

It's not the only exception to the rule but it's the one I find most unintuitive.

This is to ensure that it is not included in any previous statements since WITH can serve a variety of purposes in T-SQL.

If it's the first statement in the batch, I don't think you need it.