Quick Flashback

Back in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

I did not officially accept the challenge. Was an official acceptance required? I don’t know. I do know that I think it is a good challenge and that I intend to participate in the challenge. I hope I can meet the requirements and keep the posts to “basics”. Let’s just call this first post in the challenge to be my official acceptance.

With this being another installment in a monthly series, here is a link to review the other posts in the series – back to basics. Reviewing that link, you can probably tell I am a bit behind in the monthly series.

SQL Server CTE

Of all the fundamental concepts within SQL Server, this one drives me a bit batty from time to time. Think about that statement for just a moment. I am not going to go in depth about the syntax of a CTE.

I am not really interested in breaking down the semantics and uses of the CTE. Rather, I want to break down something commonly associated to the CTE that is far more basic. I would classify this as more of a myth and misconception than anything else.

The topic du jour happens to be around punctuation. More specifically, do we use terminators or do we use beginninators? Yes that is a made-up term, but you get the drift. Where exactly should the punctuation be placed? Better yet, is it mandatory to include that punctuation every single time?

Those are the items I wish to discuss and hopefully demystify with a few examples (if that many are even necessary).

Punctuation

Punctuation shouldn’t be too big of a deal right? There is so little punctuation within t-sql that it really should be very basic and easy to understand. That said, there is this perpetual myth that a semi-colon is required at the beginning of a CTE. Furthermore, and somewhat more accurate, is that a semi-colon is a requirement in order for a CTE to work. Let’s take a look at both of those scenarios.

Case the First

Let’s just take a look at the most basic of scripts. A simple select statement without a CTE.

USE AdventureWorks2014;
GO
SELECT TOP 10 *
FROM Person.Person

Straight away, we can see that I am accessing the AdventureWorks2014 Database. This happens to be on my 2014 instance as well. I have not terminated the select statement with a semi-colon. It really is somewhat a preference here since the statement will work with or without the semi-colon just the same (and despite the previous notion that it was going to become mandatory to terminate all statements with a semi-colon, don’t believe it until it actually happens).

Here is the SQL Server 2016 version:

use WideWorldImporters;
GO
select top 10 *
From [Sales].[Customers]

Now let’s go with a basic CTE example written the same way that we commonly see CTEs written on the internet.

;WITH proofinpoint AS (
SELECT *
FROM Person.Person

)
SELECT TOP 10 *
FROM proofinpoint pp
ORDER BY BusinessEntityID DESC;

Notice that I am using the semi-colon as a statement beginninator and terminator all in the same statement. The statement runs perfectly fine – that much is true. The usual reason for the leading semi-colon is that the cte has to be preceded by a semi-colon. Let me see how true that is with a minor tweak!

WITH proofinpoint AS (
SELECT *
FROM Person.Person

)
SELECT TOP 10 *
FROM proofinpoint pp
ORDER BY BusinessEntityID DESC;

What do you expect to happen from the execution of this statement? Well, as it stands the statement will execute without error just the same as it did in the previous example.

What? That worked? Yes. Yes it did. This is quite frankly due to the reality that a semi-colon is not required to precede a CTE. This statement was the only statement in the batch.

Case the Second

Based on those results, that must mean that a semi-colon is absolutely required when there is a statement preceding the CTE. Therefore, what is really required is the statement that immediately precedes the CTE must be terminated by a semi-colon. Not so fast there. Let’s test this one too!

Let’s use the following basic example.

USE AdventureWorks2014;
GO
;WITH proofinpoint AS (
SELECT *
FROM Person.Person

)
SELECT TOP 10 *
FROM proofinpoint pp
ORDER BY BusinessEntityID DESC;

That query works perfectly fine again. That must be due to the preceding semi-colon, right? Hold on to that thought. How about a rewrite as follows:

USE AdventureWorks2014;
GO;
WITH proofinpoint AS (
SELECT *
FROM Person.Person

)
SELECT TOP 10 *
FROM proofinpoint pp
ORDER BY BusinessEntityID DESC;

Here we tweaked the query to represent the notion that all previous statements should be terminated with a semi-colon. What do you think will happen? This will result in an error. GO is a batch terminator and therefore does not require the semi-colon. Surely this reinforces the theory that the semi-colon should come preceding to the CTE though, right? Let’s rewrite that query again.

USE AdventureWorks2014;
GO
WITH proofinpoint AS (
SELECT *
FROM Person.Person

)
SELECT TOP 10 *
FROM proofinpoint pp
ORDER BY BusinessEntityID DESC;

I removed the semi-colon and now the code segment will execute without error. Wait there is more!

I want to continue tweaking this query. Let’s try multiple selects along with this CTE.

USE AdventureWorks2014;
GO
SELECT TOP 10 *
FROM Person.Person
BEGIN
WITH proofinpoint AS (
SELECT *
FROM Person.Person

)
SELECT TOP 10 *
FROM proofinpoint pp
ORDER BY BusinessEntityID DESC;
END

Now I have a query that has a preceding select that is not terminated with a semi-colon. I have a CTE that is created right after that select and then I select from that CTE. What could ever possibly happen with this particular query?

The short of that story is that the query will execute without error and I will end up with two result sets.

Wait! Hold on two seconds there! Surely the semi-colon is an absolute requirement because we see it everywhere that it is a mandatory requirement.

The reality is that the semi-colon requirement is not really entirely accurate. If the CTE happens to be in the same batch, then the previous statement in the batch must be terminated by the semi-colon. Take the following change for example:

USE AdventureWorks2014;
GO
BEGIN
SELECT TOP 10 *
FROM Person.Person
END;
WITH proofinpoint AS (
SELECT *
FROM Person.Person

)
SELECT TOP 10 *
FROM proofinpoint pp
ORDER BY BusinessEntityID DESC;

If I remove the semi-colon from after the “END” statement, then my query will terminate in an error. If I remove the BEGIN and END from around the first select statement, then the query will fail with an error. The error that I receive is part of the reason for the huge misconception about when a semi-colon is required:

Msg 319, Level 15, State 1, Line 38
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

As I have already shown though, this is not accurate. Start thinking on a bigger scale than just semi-colons. Personally, I would prefer to see CTEs that are used in examples on the internet to be encapsulated with a BEGIN…END instead of using a preceding semi-colon (beginninator).

Has this behavior changed with SQL Server 2016? That is easily shown by the following example:

use WideWorldImporters;
GO
/* this succeeds */select top 10 *
From [Sales].[Customers]
begin
with proofinpoint as (
select top 10 *
From [Sales].[Customers]
)
select *
From proofinpoint
END
/* this succeeds */use WideWorldImporters;
GO
with proofinpoint as (
select top 10 *
From [Sales].[Customers]
)
select *
From proofinpoint;
/* this fails missing statement terminator or batch separator */use WideWorldImporters;
GO
begin
select top 10 *
From [Sales].[Customers]
end
with proofinpoint as (
select top 10 *
From [Sales].[Customers]
)
select *
From proofinpoint;

Recap

These examples clearly demonstrate the inaccuracy of the myth that a semi-colon is required. Despite the statement made in the error message, it just is not entirely the full story. A semi-colon to terminate the preceding statement within a batch is necessary but it is not required every time. If there is a separation between batches and the CTE, then a semi-colon is not required. And never is a semi-colon required to precede a CTE. It is required to terminate a previous statement within the same batch.

Please don’t be led astray by some notions on the web that dictate a CTE must be preceded by a semi-colon.