SQL Server Common Table Expression
The common table expression or the CTE is the named result set, which was introduced in SQL Server 2005. Common table expression acts as a virtual table with records and columns which is created during the execution of a query with CTE and released after completion of the query. It can be referenced within any SELECT, INSERT, UPDATE, or DELETE statement. This is used to create a view as well.
CTE can be defined by the syntax below.
As per the example, after defining the CTE CTE_Name, we can use the CTE immediately after defining it as a table. Below is an example:
It will return the output of three columns, column1, column2, and column3.
It can be used in the insert, delete, update and merge statements as well. We will show one example of each below.
Multiple CTE can be used in a single query.
The query above will return the records from the table table1 where column1 is greater than 100 and column2 is greater than 200.
Delete Using CTE
CTE can be very handy to delete records from a table.
The statement above will delete the records from the base table: table table1 where column1’s value is more than 100.
This is also the efficient way to eliminate duplicate entries from a table. Below is the example.
This will delete all the duplicate rows from the table table1.
Insert Using CTE
We can insert a specific dataset which is defined in a CTE into another table. Look to the below example.
The statement above will create the table with the three columns- column1, column2, column3 and insert data into it.
Update Using CTE
The concept of updating using CTE is the same as insertion and deletion. Let us check below example.
Merge Using CTE
Refer to the example below for a better understanding.
In the query above, we are trying to load data incrementally from the src_table to the tgt_table.
How CTE, Temp Table, and Temp Variable are Deferred in SQL Server?
From the last few examples, we get to know the usages of CTE and we got a clear idea of what is CTE. Now, the difference between CTE is and Temp table and temp variable is:
- CTE always needs memory but temp tables needs a disk. Table variable uses both. So, we should not use CTE when there is more volume of data.
- The scope of table variable is only for the batch and the scope of the temp table is for the session and the scope of CTE is only for the query.
CTE can come handy when you need to generate temporary result set and it can be accessed in select, insert, update, delete and merge statement. It can be much optimized in terms of CPU and memory usages.