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.
1) Below is the syntax to create the CTE in SQL Server with example.
Syntax of CTE in SQL Server
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.
Example of CTE in SQL Server
3) As explain earlier we use the CTE with CREATE ,
DELETE statement in SQL Server. If we DELETE or UPDATE data of CTE then that data get affected in main physical 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.
5) CTE table must use only once in the scope example. Select record from CTE two times one after another.
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.