CTE

 Common table Expression

This is used to store result of a complex sub query for further use.
CTE is when you want to save data for temporary purpose.

Use for recursive query.
1) store data
2) Recursion

--Drop Table Project
GO
CREATE TABLE Project
(  
Emp_Id INT,  
Emp_Name VARCHAR(30),  
Project_Name VARCHAR(30) ,
CreatedDateTime Datetime Default getdate()
) 
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','Survey managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','HR managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','Survey managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','HR managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','Survey managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','HR managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Internal Portal')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Wealth managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Internal Portal')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Wealth managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Internal Portal')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Wealth managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Internal Portal')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Wealth managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(3,'Roshan','Internal Portal')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(3,'Roshan','Wealth managment')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(3,'Roshan','Internal Portal')
Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(3,'Roshan','Wealth managment')

;with CTE_test
AS
(
Select * from Project 
)
Select Emp_Id from CTE_test

-----------------------------CTE Table---------------

--How to Find Duplicate Records?
With CTE 
As
(
Select Emp_Id,Emp_Name,Project_Name,
ROW_NUMBER()Over(partition by Emp_Id,Emp_Name,Project_Name Order By Emp_ID) As RN 
from Project 
)
Select * from CTE Where RN>1

--How to Delete Duplicate Records?
With CTE
As
(
Select Emp_Id,Emp_Name,Project_Name,
ROW_NUMBER()Over(partition by Emp_Id,Emp_Name,Project_Name Order By Emp_ID) As RN 
from Project 
)
Delete from CTE Where RN>1

-------------------------------------------
With CTE_Dept (Deptid,Deptname)
AS
(
SELECT [DepartmentId],[Name]
FROM [dbo].[Department]
)
Select * from CTE_Dept

----------------------combine 2 or 3 CTE

--Club 2 CTE into 1 Query       when combine comma(,) is necessary

;With CTE_Emp
AS
(
Select Id,FirstName,LastName,DepartmentId
From [dbo].[Employee]
), 
CTE_Dept
AS
(
SELECT [DepartmentId],[Name]
FROM [dbo].[Department]
)
Select * from CTE_Emp 
INNER JOIN CTE_Dept 
ON CTE_Emp.DepartmentId=CTE_Dept.DepartmentId 

----------------------CTE with recursion query:

DECLARE @Min int;  
DECLARE @Max int;  
SET @Min = 1;
SET @Max = 150;  
WITH cte AS  
(  
  SELECT @Min AS num 
  UNION ALL 
  SELECT num + 1 FROM cte WHERE num + 1 <= @Max  
)  
SELECT num FROM cte 

--Question- max recusion in CTE 
--Answer=>100

Use Case
-------Emp manager 

---------------------------------------------------------
--CTE used in Function

Create FUNCTION fnname()
RETURNS INT
AS
BEGIN
    Declare @intvar Int;
    With CTE(id)
    AS
    (
    select 555
    )
    Select @intvar=id from CTE
    RETURN @intvar
END

Select dbo.fnname() As Number

--------------------
Where it Stored? --In Memory (RAM)
Can we create Index on it? --No
Can we create constraint on it?--No
Can we Create CTE in Stored Procedure? --yes
Can we Create CTE in Function? --yes 
How to Drop it? --No need to Drop Explicit
scope--immediate after creation
Name overwritten-- In One Session, Same name is allow for 2 or more CTE