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!