Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I was just looking at a post on SO (http://stackoverflow.com/questions/12183062/how-to-ensure-contiguity-of-a-tally-table) where Aaron Bertrand proposes using a CTE instead of a numbers table, which is an elegant way of performing the task at hand. My question is, why does the first line of the CTE begin with a semi-colon?

;WITH n AS (SELECT TOP (10000) n FROM 
  (SELECT n = ROW_NUMBER() OVER
    (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
  ) AS x ORDER BY n
)
SELECT n FROM n ORDER BY n; -- look ma, no gaps!

Is this to ensure the WITH statement does not get parsed into a previous SELECT or something? I see nothing in SQL Server 2005 BOL about using a semi-colon prior to the WITH.

share|improve this question
up vote 16 down vote accepted

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:

http://msdn.microsoft.com/en-us/library/ms143729.aspx

(Search the last page for "semi-colon")

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.

share|improve this answer
1  
In 2012 even I get the same error message, but only because of the semi-colon after END TRY: i.stack.imgur.com/rc6dw.png -- if I remove that semi-colon, everything works. – Aaron Bertrand Aug 29 '12 at 18:43
    
I think you cannot put a semicolon before BEGIN CATCH simply because it is part of a single compound statement introduced with BEGIN TRY. It's same as putting a semicolon before an IF statement's ELSE. – Andriy M Aug 30 '12 at 8:07
    
@AndriyM I've been privy to a much more detailed conversation about the rules here. I mentioned it because it's a surprise to everyone who comes across it, not because I don't understand the reason. :-) – Aaron Bertrand Aug 30 '12 at 11:31
    
I think sticking to ;with gives the wrong impression to a lot of people: that ;with is a single keyword. This results in questions like this: stackoverflow.com/questions/23078215/… (I have seen several other questions that assumed that it always needs to be ;with) – a_horse_with_no_name Dec 30 '15 at 22:22
    
@a_horse_with_no_name Are you sure that's what they really assumed? I would assume they just copied and pasted the code there, rather than typed it deliberately. Anyway for every example you find like that, I can give you 20 where someone copied the code without the leading semi-colon, then complained that it caused an error. So I'm going to stick with my approach. – Aaron Bertrand Dec 30 '15 at 22:29

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.