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

Total Page Preview: 000000016555

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

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


Please enter your comment.
Please enter your Email-Id. Please enter valid Email-Id
Please enter your name.

Notify me of new comments via email. Notify me of new posts via email.

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

Brijesh Kumar

Comments

Recent Posts

Facebook User: