What’s the difference between #Temp and ##GlobalTemp?

#Temp is local to the current session, while ##GlobalTemp is accessible by all sessions until explicitly dropped.

Working with Temporary Tables in SQL | FullStackPrep.Dev

Q: What is a temporary table in SQL?

A temporary table is a short-lived table used to store intermediate data during a session or stored procedure.

💡 Concept Name

Temporary Tables are special tables in SQL used to hold intermediate results temporarily, typically within a single session or procedure.

📘 Quick Intro

Temporary tables are useful for breaking complex queries into manageable steps. They store transient data and are automatically dropped when the session ends (for local temp tables) or when manually dropped (for global ones).

🧠 Analogy / Short Story

Think of temporary tables like a scratchpad during a math exam. You jot down steps and intermediate calculations to avoid cluttering your final answer. Similarly, SQL temp tables help you temporarily store partial results while building a larger query.

🔧 Technical Explanation

#TempTable is a local temporary table visible only to the current session.
##GlobalTemp is a global temporary table visible to all sessions until closed.
Use SELECT INTO or CREATE TABLE to define a temp table.
Temp tables are stored in the tempdb system database.
They can have indexes, constraints, and be used in JOINs or stored procedures.

🎯 Purpose & Use Case

✅ Simplify complex joins or multi-step queries.
✅ Store intermediate query results for reporting logic.
✅ Improve performance by materializing expensive subqueries.
✅ Facilitate batch processing or loops in stored procedures.

💻 Real Code Example

-- Create and populate a temporary table
SELECT CustomerID, OrderTotal
INTO #CustomerSales
FROM Orders
WHERE OrderDate >= '2024-01-01';

-- Query the temporary table
SELECT CustomerID, SUM(OrderTotal) AS TotalSpent
FROM #CustomerSales
GROUP BY CustomerID;

-- Drop the temp table
DROP TABLE #CustomerSales;

❓ Interview Q&A

Q1: What is a temporary table in SQL?
A: A special table used to hold intermediate results during a session, usually deleted automatically afterward.

Q2: How do you create a temporary table?
A: You can use SELECT INTO or CREATE TABLE #Temp to define one.

Q3: Where are temporary tables stored?
A: In the tempdb system database in SQL Server and similar structures in other RDBMSs.

Q4: What is the difference between # and ## temp tables?
A: #Temp is local to the session; ##Global is visible to all sessions.

Q5: Can you index a temp table?
A: Yes, you can create indexes to speed up queries on temporary tables.

Q6: When are temp tables dropped automatically?
A: Local temp tables are dropped when the session ends or connection closes.

Q7: Are temp tables better than CTEs?
A: For repeated use or indexing, temp tables are better; for inline logic, CTEs are more concise.

Q8: Do temp tables affect performance?
A: They can improve performance when used correctly, especially for repeated computations.

Q9: Can temp tables be used in joins?
A: Yes, they work like regular tables in JOIN, WHERE, and GROUP BY clauses.

Q10: Are temp tables secure between users?
A: Yes, local temp tables are session-specific and cannot be accessed by other users.

📝 MCQs

Q1. What prefix is used for local temporary tables?

#
##
@
$

Q2. Where are SQL Server temp tables stored?

master
model
tempdb
msdb

Q3. Which type of temp table is visible to all sessions?

#Temp
##GlobalTemp
CTE
@TableVar

Q4. How do you remove a temp table manually?

DELETE
TRUNCATE
CLEAR TABLE
DROP TABLE

Q5. Which is true about local temp tables?

Used for backups
Persist after shutdown
Only visible to current session
Encrypted by default

Q6. What is a benefit of temp tables?

Encrypt data
Run triggers
Simplify complex logic
Create indexes on views

Q7. Which symbol denotes a global temp table?

@@
#
##
$$

Q8. Are temp tables allowed in stored procedures?

No
Only global ones
Yes
Only in CTEs

Q9. Which operation is NOT valid on temp tables?

JOIN
GROUP BY
ORDER BY
Permanent ALTER after drop

Q10. Can temp tables have indexes?

No
Yes
Only if global
Only clustered

💡 Bonus Insight

Use temp tables when working with large intermediate datasets or performing complex aggregations across multiple steps. When performance is critical, consider indexing columns used in joins or WHERE conditions. Always clean up with DROP TABLE when done.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

📄 PDF Download Coming Soon

Working with Temporary Tables in SQL