SQL Server Interview Questions on Temporary Tables

What are the 2 types of Temporary Tables in SQL Server?
1. Local Temporary Tables
2. Global Temporary Tables

What is the difference between Local and Global Temporary Tables?
Local Temporary Tables:
1. Prefixed with a single pound sign (#). 
2. Local temporary tables are visible to that session of SQL Server which has created it. 
3. Local temporary tables are automatically dropped, when the session that created the temporary tables is closed.

Global Temporary Tables:
1. Prefixed with two pound signs (##). 
2. Global temporary tables are visible to all the SQL server sessions. 
3. Global temporary tables are also automatically dropped, when the session that created the temporary tables is closed.

Can you create foreign key constraints on temporary tables?
No

Do you have to manually delete temporary tables?
No, temporary tables are automatically dropped, when the session that created the temporary tables is closed. But if you maintain a persistent connection or if connection pooling is enabled, then it is better to explicitly drop the temporary tables you have created.
However, It is generally considered a good coding practice to explicitly drop every temporary table you create.



In which database, the temporary tables get created?
TEMPDB database.

How can I check for the existence of a temporary table?