CTE in SQL server - WebNetGuru.com

CTE in SQL server


CTE stands for Common Table Expression. CTE in SQL Server allows to create table expression by giving temporary name to result set. CTE allows us to give the name to existing result set and use that result set as table.

Below are characteristics of CTE in SQL Server.

Using CTE we can create the table expression which is used with the DML statement like CREATE, INSERT, SELECT , UPDATE and DELETE.

1) Below is the syntax to create the CTE in SQL Server with example.

Syntax of CTE in SQL Server

; WITH < CTE table name > (column 1, column 2, ...)
AS
(
SQL Server Statement.
)

2) CTE use the WITH keyword to create the table expressions, we need to write the semicolon (;) before WITH keyword.
Consider we have physical student table, and we have to find out all 10th std student and whose name start with 'V' char. Let achieve this using CTE.

create table using CTE in SQL Server

Example of CTE in SQL Server

; WITH cteTable ( Name, Contact )
AS
(
SELECT Name, Contact
StudentName FROM (100) cteTable WHERE Name Std = 10
)
SELECT Name, Contact StudentName FROM (100) cteTable WHERE Name LIKE 'V%'

Output

create table using CTE in SQL Server


3) As explain earlier we use the CTE with CREATE , INSERT , SELECT , UPDATE and DELETE statement in SQL Server. If we DELETE or UPDATE data of CTE then that data get affected in main physical table.


Output

perform DML operation using CTE in SQL Server
Or we can use CTE to delete duplicate record from table.

4) Scope of CTE in SQL Server is very limited.
CTE table must use immediate after it create else it will give an error.


Output

scope of CTE in SQL Server

5) CTE table must use only once in the scope example. Select record from CTE two times one after another.

scope of CTE in SQL Server

Note : Interview question may ask that is If we perform the DML operation like INSERT, UPDATE, DELETE on CTE table, does it effect on physical table or main table ? Answer is Yes.
For example. Find point not 3 above.


To read more about CTE in SQL Server refer Microsoft.

Move Top