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!

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, ctes, language sql, sql statements, T-SQL