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



Comments


                           
                           
                           
                           

Recent Posts

What is the difference between delete and truncate statements in SQL in English and Hindi
09-Apr-2023
 Explain difference between Union and Union all in SQL in English and Hindi
12-May-2023
 What is temporary table in SQL in English and Hindi
12-May-2023
 What is SQL group by function in English and Hindi
22-Apr-2023
 What is order by function in SQL in English and Hindi
23-Apr-2023
 What is Having function in SQL in English and Hindi
07-May-2023
 What is concat function in SQL in English and Hindi
16-Apr-2023
 What is SQL select statement in English and Hindi
08-Apr-2023
 What is Auto increment in SQL in English and Hindi
18-Apr-2023
 What is cross join in SQL in English and Hindi
16-Apr-2023
 What is replace function in SQL in English and Hindi
16-Apr-2023
 What is count function in SQL in English and Hindi
16-Apr-2023
 What is drop statement in SQL in English and Hindi
18-Apr-2023
 What is SQL insert statement in English and Hindi
08-Apr-2023
 What is SQL where clause in English and Hindi
11-Apr-2023
 What is SQL aliases in English and Hindi
13-Apr-2023
 What is SQL Stored Procedures for SQL Server in English and Hindi
11-Apr-2023
 What is SQL delete statement in English and Hindi
14-Apr-2023
 How to convert date function and format in SQL in English and Hindi
14-Apr-2023
 What is SQL update statement in English and Hindi
08-Apr-2023

Facebook User: