Difference Between Temporary Tables and CTEs in SQL
💡 Concept Name
Temporary Table and Common Table Expression (CTE) are both used to store intermediate query results, but differ in scope, performance, and use cases.
📘 Quick Intro
Temporary tables are physical tables stored in tempdb that persist for the session or procedure, while CTEs are temporary named result sets within a single query's execution scope.
🧠 Analogy / Short Story
Think of a temporary table as a whiteboard you set up in a meeting room to jot down ideas for a whole session, and a CTE as a sticky note you use for a quick reminder within one conversation. The whiteboard stays visible and modifiable during the session, while the sticky note disappears once the discussion ends.
🔧 Technical Explanation
- 📝 Temporary tables physically store data and can be indexed, allowing reuse across multiple queries in the session.
- 🧩 CTEs are virtual result sets defined within a query, existing only during that query's execution.
- 🔄 Temporary tables support data modification (INSERT, UPDATE, DELETE); CTEs are read-only.
- ⏳ Temporary tables persist beyond single query and can improve performance with complex operations.
- 🚀 CTEs simplify query readability and recursion but may be less performant for large intermediate results.
🎯 Purpose & Use Case
- ✅ Use temporary tables when you need to reuse or modify intermediate results multiple times.
- ✅ Use CTEs for simpler, readable queries or recursive queries that don't require storing data.
💻 Real Code Example
-- Temporary Table example
CREATE TABLE #TempSales (
SalesPersonID INT,
TotalSales MONEY
);
INSERT INTO #TempSales
SELECT SalesPersonID, SUM(SalesAmount)
FROM Sales
GROUP BY SalesPersonID;
SELECT * FROM #TempSales WHERE TotalSales > 10000;
-- Common Table Expression (CTE) example
WITH SalesCTE AS (
SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
)
SELECT * FROM SalesCTE WHERE TotalSales > 10000;
❓ Interview Q&A
Q1: What is the main difference between a temporary table and a CTE?
A: Temporary tables are physical tables stored temporarily in the database, while CTEs are temporary result sets defined within a query.
Q2: Can you modify data in a CTE?
A: No, CTEs are read-only and cannot be modified.
Q3: When should you prefer temporary tables over CTEs?
A: When you need to reuse or update data multiple times within a session or procedure.
Q4: Do temporary tables improve performance?
A: They can, especially for complex queries or large datasets by avoiding repeated calculations.
Q5: Can CTEs be recursive?
A: Yes, CTEs support recursion to handle hierarchical data queries.
📝 MCQs
Q1. Which of the following is true about temporary tables?
- They are virtual views
- They are physical tables stored in tempdb
- They are permanent tables
- They are read-only result sets
Q2. Can you update data inside a CTE?
- Yes
- No, CTEs are read-only
- Only with triggers
- Depends on DBMS
Q3. Temporary tables persist for?
- Only one query
- The session or procedure
- Until server restart
- Forever
Q4. What is a key advantage of CTEs?
- Stores data physically
- Improves query readability
- Can be reused multiple times
- Supports indexing
Q5. Can temporary tables have indexes?
- No
- Yes
- Only clustered indexes
- Only primary keys
💡 Bonus Insight
While temporary tables and CTEs can both simplify complex queries, choosing between them depends on the specific use case, data volume, and performance considerations.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!