Difference between CTE and Temp Table

CTE

• CTE is  un-materialized/ non-indexable (cannot create indexes on CTE)
• CTE is logical/disposableView
• CTE persists only till the very next query
• CTE cannot have constraints
• CTE is mostly used for recursion, as CTE can call itself
• CTE resists in memory

 Example 

By using CTE

    ;With CTE1(Addr, FullName, Age)
    AS
    (
    SELECT Addr.Addr, Emp.FullName, Emp.Age from Address Addr
    INNER JOIN Employee Emp ON Emp.EID = Addr.EID
    )
    SELECT * FROM CTE1 --Using CTE
    WHERE CTE1.Age > 50
    ORDER BY CTE1.FullName

Temp Table

• Temp table gets stored in temp table
• Temp table persists till the current connection ends
• Temp table can be referred in sub procedure
• Temp table can have constraints,indexes and primary defined
• Indexes can be implemented in Temp Table
• Data can be updated in Temp Table
• Temp Tables are stored in disk

Example

Temp Table

    CREATE TABLE #Temp
    (
    UID int,
    FullName varchar(50),
    Addr varchar(150)
    )
    GO
    insert into #Temp values ( 1, 'Raj','Pune');
    GO
    Select * from #Temp