Total Page Preview:   000000013458

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 : https://www.linkedin.com



Comments


                           
                           

                           

                           

Facebook User: