Difference between Temporary table, Table Variable and CTE
Temp Table
- Temporary table are just like normal tables, but are created in TempDB.
- They persists until dropped or the connection is lost that created them stops.
- They can have primary tables, constraints, indexes and column statistics are kept for the table
- Usually temp table have assigned space in the TempDB but they are accessed directly from memory unless the server is under memory pressure or amount of data is large in the table
Table Variable
- They are created when they are declare and destroyed once theygo out of scope. They cannot be explicitly destroyed
- Just like temp tables they also reside in TempDB
- Table variables can have primary keys but indexes cannot be created on them neither statistics can be maintained on columns
CTE
- CTE are more like temp views
- In execution plan they get inlined into a query
