Writing Your First Common Table Expression with SQL Server

Download PDF

Day 2 of Common Table Expression Month (June) at SteveStedman.com, today I will cover creating your very first CTE.

Keep in mind that this may look a bit weird or strange at first, the syntax is different from anything you may have seen with SQL Server before, and there are some parts that are very similar.

First lets take a look at a Non-CTE query using a derived table subquery. These queries 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.

First a simple SELECT statement to see what is in the Departments table.  We will be using the Departments table in the CTE queries.


SELECT *

FROM Departments;

CTE_my_first_cte_1

Derived Table Example

A derived table, often referred to as a subquery is the placement of a query inside of another query instead of a table name. It looks like this:


SELECT *
FROM (SELECT id, department, parent FROM Departments) as Dept;

It is likely that if you have used T-SQL that this may be familiar to you.  When the derived table query is run, it produces the same thing as the original query because we are just selecting everything from the original query, however derived table queries are often more complex than this example.

CTE_my_first_cte_1

In the derived table, example it doesn’t do anything more than the original query, it is just a quick introduction to the derived table.

CTE Query

Similar to the derived table example, your first CTE will be very straightforward. The CTE query starts with the WITH keyword instead of a SELECT keyword.

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

Which produces the following results:
CTE_my_first_cte_2

You could just copy and paste the CTE query above and run it in SQL Server Management Studio, but first lets look at the parts that make up the CTE.

First the CTE starts with a WITH statement. You can included a ; in front of the WITH statement to help ensure that the previous statement was properly terminated.

CTE_WITH

Next we name the CTE, in this case the name of the CTE is departmentsCTE.  You can tack the CTE acronym on to the end of the CTE name to avoid confusion with similar table names.

CTE_Parts2

Next is the optional columns list. For a simple CTE, this column list can be skipped and is not required.

CTE_Parts3

Then the AS keyword separates the CTE declaration from the CTE query.

CTE_Parts4

Following the AS statement is an open parenthesis ( the CTE query, and then a closing parenthesis ). Here the CTE query is just selecting 3 columns from the departments table.

CTE_Parts5

And finally the query that calls or runs the CTE. Here we are just selecting all columns from the CTE that was defined above.

CTE_Parts6

Put it all together and you get the following output:

CTE_my_first_cte_2

At this point it is not much more interesting than the derived table example, or even the simple select statement. This has been intended as just an overview or introduction to the anatomy of a SQL Server Common Table Expression. Tomorrows post will start showing some interesting things you can do now that you understand the simple CTE.

Related Links:

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.

Tagged with: , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.