A CTE is a temporary view that exists only in the scope of running query. A CTE is similar to a view but a CTE is embedded into the query. CTEs can be used in Stored Procedures, User Define Functions (UDFs), Triggers and Views but not in Indexed Views.

Below is the syntax for a CTE :
In the database, you will have a physical table called Sales.Customer and from the above query you are taking that table into a CTE called AllmyCustomers which will be referred in a future query.

Difference between a CTE and Variable Table

Obviously, a CTE is different from a temporary table sice temporary tables save data to the tempdb but the CTE will be kept in the memory. So are is the differences between CTEs and table variables?

* * A table variable’s scope is for the batch, while a CTE’s scope is only for the query.
* To populate a table variable, you need to execute insert scripts, but with CTEs the data will be populated at time of defining the CTE.
* If you want to use recursion in table variables, you need to write your own queries but with a CTE it is much easier to write recursive queries.
* If you are using larger data sets in table variables it will use tempdb to store data physically. However, a CTE always uses memory. This can be an issue in electing for CTEs as you need to ensure that you are not using larger data sets that will drain memory. The following graph shows tempdb write transactions/sec for temp tables, table variables and CTE scenarios.

* You can simply see that a CTE does not write anything to the tempdb, hence it has a higher performance compared with the other two options.

Usages for CTE

There are a few scenarios where using CTEs is optimal:

Recursion

Recursion is typically a difficult development task. Let us look atan example, say we have following data set:

IF EXISTS (SELECT 1 FROM sys.tables WHERE Name = 'Employees')
DROP TABLE Employees
CREATE TABLE Employees
(EmployeeID INT PRIMARY KEY,
Name VARCHAR(50), ReportsTo INT)


The important point to remember is ReportsTo is foreign key constraint to the EmployeeID column or as some of you call it, this table has a self-relationship between two columns of the same data.

We will now populate this with the following sample data:
INSERT INTO Employees
VALUES
(1,'Richard',NULL),
(2,'Stephan',1),
(3,'Clemens',2),
(4,'Simmon',2),
(5,'Anderson',4),
(6,'Cris',1),
(7,'Andrew',5),
(8,'Peter',6)

If you visualize your data, it will looks as below.

Full article...