Saturday, 12 October 2013

Difference between temp table and CTE

                                            Difference between temp table and 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

No comments:

Post a Comment