Why Should a CTE Start with a Semi-Colon in SQL Server?

ctesql serversyntaxt-sql

I was just looking at a post on StackOverflow 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.

Best Answer

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.

Related Solutions

Sql-server – reason the CTE with a basic subquery in the second stage of the CTE is failing

Let's pretend for a moment that CTE_RealAudit is a normal table...

You're querying it twice! Once to get the list of Minutes values, and to get all the other fields for those rows that have a Minutes value (oh, and surely it always will have, unless you have NULLs) in the list. So yes, that's going to take a lot longer than querying it once.

So that's why it's taking much longer. But I don't think you need to query it twice at all - have a good think about the logic you actually want, especially as it's not a normal table, but rather a complex subquery over a view.

SQL Server – Difference Between WITH CTE and WITH CTE ()

You nearly have the answer for one of your questions already.

In the MSDN page, there is a line directly after your quote that explains this:

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

(Emphasis added)

This would mean that you would need to specify column names in a few situations:

This would work:

WITH [test_table] ([NoName], [CAST], [Function]) 
AS
(
    SELECT 
        1
      , CAST('1' AS CHAR(1))
      , dbo.CastToChar(1)
)
SELECT * FROM [test_table];

as would this:

WITH [test_table]  
AS
(
    SELECT 
        1 as [NoName]
      , CAST('1' AS CHAR(1)) as [CAST]
      , dbo.CastToChar(1) as [Function]
)
SELECT * FROM [test_table];

But this would not as it does not have distinct names for the columns:

WITH [test_table] 
AS
(
    SELECT 
        1
      , CAST('1' AS CHAR(1))
      , dbo.CastToChar(1)
)
SELECT * FROM [test_table];

Best Answer

Related Solutions

Sql-server – reason the CTE with a basic subquery in the second stage of the CTE is failing

SQL Server – Difference Between WITH CTE and WITH CTE ()

Related Question