Total Page Preview:   000000016145

Difference between CTE(Common Table Expressions) and Temp Table in SQL Server

Difference between CTE(Common Table Expressions) and #Temp Table in SQL Server :
  CTE #Temp Table
1 Are unindexable (but can use existing indexes on referenced objects). Are real materialized tables that exist in tempdb
2 Cannot have constraints Can be indexed
3 Are essentially disposable VIEWs Can have constraints
4 Persist only until the next query is run Persist for the life of the current CONNECTION
5 Can be recursive Can be referenced by other queries or subprocedures
6 Do not have dedicated stats (rely on stats on the underlying objects) Have dedicated stats generated by the engine
Defference CTE And Temp Table in SQL Server

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn :





Facebook User: