Difference between temp table and CTE

                                            Difference between temp table and CTE

 CTE:                 

• CTE is  un-materialized/ non-indexable (cannot create indexes on CTE)
• CTE is logical/disposable View
• 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

 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

Similar Posts:   Difference between CTE and View  ,  CTE(Common Table Expressions) in SQL ,                                      T- SQL Interview Questions and Answer