SQL Server Interview Questions on Temporary Tables


share this interview article on:

What is Temporary Table?

The concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.

 

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


When to use Temporary table?

Below are the scenarios where we can use temporary tables:

  • When we are doing large number of row manipulation in stored procedures.
  • This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
  • When we are having a complex join operation.

 

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.
 



Related Interview Questions

ADS

Tags:

temporary sql interview