What is a CTE?

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH TestCTE (id)
AS
(
SELECT id  FROM table
)
SELECT * FROM TestCTE 

A CTE can be used in place of a view in some instances.