Good article. Well articulated.

I would just like to state that perhaps the following point about CTE usage should have been mentioned as well since that is something which, if a person is not aware of, can lead to unnecessary headaches!!

** The query using the CTE must be the first query appearing after the CTE.

For ex, based on your query in Listing 1, we couldn't do the following:

WITH emp AS (

SELECT EmployeeID, FirstName, LastName, E.Title, ManagerID

FROM HumanResources.Employee AS E

INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID

)

-- First query after CTE not using the CTE

SELECT * FROM HumanResources.Employee

-- Second query after CTE using the CTE will return the "Invalid column name" error message

SELECT A.EmployeeID, A.FirstName, A.LastName, A.Title,

A.ManagerID, B.FirstName AS MgrFirstName,

B.LastName AS MgrLastName, B.Title AS MgrTitle

FROM emp AS A INNER JOIN emp AS B ON A.ManagerID = B.EmployeeID;