Total Page Preview:   000000015354

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

How to get the alternate rows or records from table in sql server
07-Jun-2017
 Data Warehouse Three Tier Architecture
20-Aug-2017
 SQL Server - Fragmentation on azure sql database
07-Oct-2018
 Limitations of Azure SQL Database
19-Sep-2019
 PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
 SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
 SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
 Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
 How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
 What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
 Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
 Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
 What is a Trigger and type of trigger in SQL server?
10-Sep-2021
 How many types of dimension tables available in data warehouse?
16-Sep-2021
 Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
 What are the differences between OLTP and OLAP?
07-Sep-2021
 What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
 What are the different types of Error Handling in SQL Server?
11-Sep-2021
 Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
 What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: