What is a CTE

1

April 6, 2015 by Kenneth Fisher

CTEs (Common Table Expressions) are one of the most interesting and useful tools added to T-SQL in the last decade. But even though they have been around for that decade and are widely used I still find that they confuse people somewhat. Among other things this confusion leads to difficulty understanding how to use them with queries other than SELECT or how to use them beyond a single CTE at a time.

Over the years I’ve developed a way of describing them to other people that seems to help alleviate some of the confusion. I tell people that they are essentially in-line views. I.E. a view definition that only exists within a single query.

Take a look at this:

--*********************************************
--********* Begin CTE Definition **************
--*********************************************
WITH
	cteTotalSales (SalesPersonID, NetSales)
	AS
	(
		SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
		FROM Sales.SalesOrderHeader 
		WHERE SalesPersonID IS NOT NULL
		GROUP BY SalesPersonID
	)
--*********************************************
--********** End CTE Definition ***************
--*********************************************
--************** Begin Query ******************
--*********************************************
INSERT INTO test
	SELECT
		sp.FirstName + ' ' + sp.LastName AS FullName,
		sp.City + ', ' + StateProvinceName AS Location,
		ts.NetSales 
	FROM Sales.vSalesPerson AS sp
	INNER JOIN cteTotalSales AS ts
		ON sp.BusinessEntityID = ts.SalesPersonID
	ORDER BY ts.NetSales DESC
--*********************************************
--*************** End Query *******************
--*********************************************
--*********** End Scope of CTE ****************
--*********************************************

Obviously they aren’t really views, they are Common Table Expressions, but to all intents and purposes you can treat them just like a view. At least in terms of the single query they are defined for. (Yes I realize you can’t index them or anything else like that.) In terms of multiple CTEs at once, as soon the CTE is defined then it is available to be used at any point until the end of scope (the query). In other words the first CTE can be referenced in the second. The first or second can be referenced in the third etc.

Hopefully this makes it easier to understand what a CTE is and how to use it. They really aren’t all that difficult once you think about them the right way and wow can they be handy!

One thought on “What is a CTE

  1. […] Master Key Re-Inventing the Recursive CTE – Added to Curah Exam 70-461 Work with data (27%) What is a CTE – Added to Curah Exam 70-461 Work with data (27%) CTEs, Inline Views, and What They Do – Added to Curah Exam 70-461 Work with data (27%) […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: