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.

[WITH [...]]
Name of cte [(name of columns [,...])]
AS ( query of cte)
Select * from CTE

 
Example:

WITH CTE_Name (column1, column2, column3)
As
(
Select column1, column2, column3
From table1
Where column1>500
)

 
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:

Select from CTE_Name

 
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

Multiple CTE can be used in a single query.

WITH CTE_Name1 (column1, column2, column3)
As
(
Select column1, column2, column3
From table1
Where column1>100
)

AS
(
select * from cte_name2
where column2>200
)
select * from cte_name2

 
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.

WITH CTE_Name (column1, column2, column3)
As
(
Select column1, column2, column3
From table1
Where column1>100
)
Delete from CTE_Name

 
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.

WITH CTE_Name (id, column1, column2, column3, rn)
As
(
Select id, column1, column2, column3, row_number() over(partition by ID order by id) as RN
From table1
)
Delete from CTE_Name
Where CTE_Name. RN > 1

 
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.

With CTE_insert (id, column1, column2, column3)
As
(
Select id, column1, column2, column3
From table1
Where column1>200
)

/* for insertion in an existing table dest_table*/

Insert into dest_table (column1, column2, column3)
Select column1, column2, column3 from cte_insert

/* For creating a new table dest_table_new and insert the data of the CTE */

Select column1, column2, column3
Into dest_table_new

 
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.

With CTE_update (id, column1, column2, column3)
As
(
Select id, column1, column2, column3
From table1
Where column1>200
)

/* Update the base table- table1, of CTE to increase the value of column1 by 100*/
update cte_update
set column1=column1+100

/*Update another table - dest_table, using the value of CTE*/
update a
set a.column1=b.column1
from dest_table a
join cte_update b
on a.id=b.id

 

Merge Using CTE

Refer to the example below for a better understanding.

WITH src_cte (id, column1, column2, column3)
AS
(
    SELECT id, column1, column2, column3 FROM src_table
)
MERGE  
 tgt_tbl AS target
USING src_cte AS source  
ON (target.id = source.id)
WHEN MATCHED THEN    
    UPDATE SET target.Column1 = source.Column1,
               target.Column2 = source.Column2,
               target.Column3 = source.Column3
WHEN NOT MATCHED THEN
    INSERT (Column1,column2, column3) VALUES (Source.Column1, Source.Column2, Source.Column3);

 
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.

Conclusion

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.

from https://ift.tt/5Rbled1