CTE Scope

Day 5 of Common Table Expression Month (June) at SteveStedman.com, today I will cover concepts around the scope of a CTE.

The queries in the video will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

Where is live is not to far from the US/Canadian border. On most days I have no need to visit Canada, but occasionally I may got to Vancouver for a SQLSaturday, or just to go out for some sushi or a comedy club. As long as I have my passport with me, I have the ability to cross into Canada and return to the United States. If I don’t plan ahead and take my passport with me, then visiting Canada is not an option, it is just not allowed. When we talk about scope in any programming language it is similar to not being able to get to that sushi restaurant inVancouver BC because I don’t have my passport and I can’t cross the border without it. That sushi restaurant would be considered out of scope. In programming languages, including T-SQL, scope is the concept of what you can or can’t access. Some things in T-SQL are accessible from anywhere on the SQL Server (assuming permissions have been granted), for instance a table has what I would call global scope in that it can be referenced from any query accessing the SQL Server (again assuming the correct permissions). If you look at something like a temporary table, it has the scope of the function or query batch that created it, there is also a concept of a global temporary table which has a greater scope.

One of the confusing things around CTE is the scope of a CTE. Given that a CTE is described as being similar to a temporary table, like a temporary named result set, or like a temporary view, all of these imply something that might be around for more than a single query. When using CTE’s, the CTE only exists for the single statement that is using it.

The scope of the CTE is only a single query. That means that the single select, insert or update statement that uses the CTE is the only thing that can access that CTE.

Lets take a look at scope using the following query:


USE [cte_demo];
 GO

SELECT *
FROM Departments
ORDER BY id;

;WITH departmentsCTE(id, department, parent) AS
 ( SELECT id, department, parent
 FROM Departments)
 SELECT * FROM departmentsCTE WHERE id = 1;

SELECT * FROM departmentsCTE WHERE id = 2;

SELECT department
 FROM Departments
 ORDER BY id DESC;

At first glance this batch of SQL may look fine, but when we run it it throws an error.

Even thought we get two result sets.

Lets take a look at the code again, so we can see what is in scope and out of scope. The green rectangle outlines a single query that is using a CTE, and that CTE is available only inside of that single statement. The red rectangle is attempting to access the CTE from the previous query, and the CTE is not reachable, it is out of scope.

If we take a look again at the original query, what can we do to achieve the results that were intended here, 4 result sets returned, and the 2nd and 3rd queries using the CTE.


USE [cte_demo];
GO

SELECT *
FROM Departments
ORDER BY id;

;WITH departmentsCTE(id, department, parent) AS
( SELECT id, department, parent
FROM Departments)
SELECT * FROM departmentsCTE WHERE id = 1;

SELECT * FROM departmentsCTE WHERE id = 2;

SELECT department
FROM Departments
ORDER BY id DESC;

To make it work we just need to copy the CTE and paste it in to the second query so that it looks like this:


USE [cte_demo];
GO

SELECT *
 FROM Departments
 ORDER BY id;

;WITH departmentsCTE(id, department, parent) AS
( SELECT id, department, parent
 FROM Departments)
SELECT * FROM departmentsCTE WHERE id = 1;

;WITH departmentsCTE(id, department, parent) AS
( SELECT id, department, parent
 FROM Departments)
SELECT * FROM departmentsCTE WHERE id = 2;

SELECT department
 FROM Departments
 ORDER BY id DESC;

Which produces the desired result sets:

Here is how the scope of the CTE’s works. The first CTE query, the CTE is only available int the green rectangle, and the second CTE only has the scope of the blue rectangle.

I hope this helps with the overall understanding of the scope of a common table expression.

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Related Links:

Common Table Expressions Book

Leave a Reply Cancel reply