SQL - Common Table Expression

Normally, Common Table Expression is known as CTE in SQL Server which was introduced in SQL Server 2005 released. CTE's are a great feature of SQL which can be used anywhere to make the query easier to read. For the better understanding, you can reference them as a temporary result set which is defined within the execution scope of a single SQL statement such as SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW.

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. It can be self-referencing or can be referenced multiple times in the same query. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.  You can be used CTE for the following purposes: 

• It can be used in recursive query.
• It is the best substitute for a view when the general use of a view is not required.
• It enables grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access. 
• Reference the resulting table multiple times in the same statement.
• CTE is the best option to avoid the temporary tables in the views because we can not use temp tables in the views.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Simply, CTEs are based on the Duration of the Query which means we cannot use the same CTE in two different SQL statements as everyone is a separate query. A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

If you need a CTE and want to reuse its results then you could always insert the results into a temp table or table variable. So it is easy to use one or the other or both.

CTE -Multiple times used example-

A recursive CTE is one that references itself within that CTE. The recursive CTE is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy. A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure, but bear in mind that it will need to be recreated every time it's needed.

CTE's are a great way of declaring a set! Not being able to index a CTE is actually a good thing because you don't need to! It's really a kind of syntactic sugar to make your query easier to read/write.

----- declare variable to store values declare @StartDate datetime,  @EndDate datetime ----- Set values in the defined local variable here select @StartDate=getdate(),  @EndDate = getdate()+10 ------ using CTE to show multiple use itself ;WITH CTE AS (                SELECT @StartDate AS myDate                UNION ALL                ------ calling multiple time here                SELECT DATEADD(Day,1,myDate)                FROM cte                WHERE DATEADD(Day,1,myDate) <=  @EndDate                ) ----- View the result here Select * from CTE;

Work with CTE:

To understand the functionality of CTE, we are taking the two tables as Employee Master and Department Master and both tables are linked with Dept Id key.

Employee Master
Emp Id	Employee Name	Dept Id	EmployeeRatePerDay
E0001	Ryan Arjun	D001	240
E0002	Tony Towery	D001	230
E0003	Lucy Gray	D001	250
E0004	Will Smith	D002	245
E0005	Chao Milk	D002	225
E0006	Chris Gyal	D002	210
E0007	Bill Gray	D003	190
E0008	Red Bill	D003	210
E0009	Tom Ramsay	D003	200

Department Master
Dept Id	Department Name
D001	Database
D002	Testing
D003	IT

----- Create CTE table to store values from employee master ;With CTE_Employee as ( SELECT [Emp Id]       ,[Employee Name]       ,[Dept Id]       ,[EmployeeRatePerDay]   FROM [dbo].[EmployeeMaster]   ),   ----- Create another CTE table to store values from department master   CTE_Department AS   (   SELECT [Dept Id]       ,[Department Name]   FROM [dbo].[DepartmentMaster]   )   ----- join both CTE table to get the output   SELECT                 ecte.[Emp Id],                 ecte.[Employee Name],                 ecte.[Dept Id],                 dcte.[Department Name],                 ecte.EmployeeRatePerDay  FROM          ----- Employee CTE table                   CTE_Employee ecte                 INNER JOIN   ----- Department CTE table                 CTE_Department dcte                 ON ecte.[Dept Id] = dcte.[Dept Id];

Output of the CTE query:

Emp Id	Employee Name	Dept Id	Department Name	EmployeeRatePerDay
E0001	Ryan Arjun	D001	Database	240
E0002	Tony Towery	D001	Database	230
E0003	Lucy Gray	D001	Database	250
E0004	Will Smith	D002	Testing	245
E0005	Chao Milk	D002	Testing	225
E0006	Chris Gyal	D002	Testing	210
E0007	Bill Gray	D003	IT	190
E0008	Red Bill	D003	IT	210
E0009	Tom Ramsay	D003	IT	200

Conclusion-

CTEs are unindexable (but can use existing indexes on referenced objects) and they cannot have constraints and are essentially disposable VIEWs. The best part of the CTEs that they persist only until the next query is run and you can used them as recursive or non-recursive also. A CTE should never be used for performance. You will almost never speed things up by using a CTE, due to just having disposable view behavior. They are best for doing some neat things and they do not have dedicated stats (rely on stats on the underlying objects).

Finally, CTEs don't use tempdb by default so you reduce contention on that bottleneck through their use.
To know more on the different kinds of the tables in SQL Servers at

1. SQL – Wide Tables
2. SQL - Table Variables
3. SQL - Temp Table (Local & Global Temp Tables)
4. SQL - Common Table Expression
5. SQL - Difference between Table Variable and Common Type Expression
6. SQL - Difference between Temp Table and CTE
7. SQL - Difference between Temp Table and Table Variable