SQL Server Temporary Tables Interview Questions

Back to: Dot Net Interview Questions and Answers

SQL Server Temporary Tables Interview Questions and Answers

In this article, I will discuss most frequently asked SQL Server Temporary Tables Interview Questions and Answers. Please read our previous article where we discussed the most frequently asked SQL Server Interview Questions with answers. As part of this article, we are going to discuss the following interview questions on SQL Server Temporary Tables.

1. What are Temporary Tables in SQL Server?
2. Types of Temporary Tables in SQL Server.
3. What is a Local Temporary Table?
4. What are Global Temporary Tables in SQL Server?
5. What are the differences between Local and Global Temporary Tables?
6. Can you please explain when to use a temp table in SQL Server?
7. Can you create foreign key constraints on temporary tables?
8. Do you have to manually delete temporary tables?
9. In which database, temporary tables get created?
10. How can I check for the existence of a temporary table?
11. How to copy the structure of the temporary table from one table to another?
12. What is the difference between a Temporary Table and a Table variable in SQL Server? 

What are Temporary tables?

SQL Server provides the concept of the temporary table which helps us in a great way. These tables can be created at runtime and can do all kinds of operations that one normal table can do. But, based on the table types, the scope is limited.

Permanent tables get created in the database we specify and remain in the database permanently until we delete (drop) them. On the other hand, temporary tables get created in the TempDB database and are automatically deleted, when they are no longer used.

What are the 2 types of Temporary Tables in SQL Server?

1. Local Temporary Tables
2. Global Temporary Tables

What is a Local Temporary Table?

the Local temp tables are only available to the current connection for the user, and they are automatically deleted when the user disconnects from instances. The local temporary table name is stared with hash (“#”) sign.

Creating a Local Temporary table is very similar to creating a permanent table, except that we prefix the table name with 1 pound (#) symbol. In the example below #PersonDetails is a local temporary table, with Id and Name columns.

For example:
CREATE TABLE #PersonDetails(Id Int, Name Varchar(50))
Inserting values into Local Temporary Tables
Insert into #PersonDetails values (1,’Mike’)
Insert into #PersonDetails values (2,’John’)

View the Tables data
Select * from #PersonDetails

What are Global Temporary Tables in SQL Server?

Global Temporary tables name starts with a double hash (“##”). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed. To create a Global temporary table prefix the name of the table with 2 pounds (##) symbols. For example.

The Global Temporary Tables are visible to all the connections of the SQL Server and are only destroyed when the last connection referencing the table is closed.

Multiple users across multiple connections can have Local temporary tables with the same name but Global Temporary Table Names should be unique and if we inspect the name of the Global Temporary Table in the object explorer there will be no random numbers suffixed at the end of the table name.

What are the differences between Local and Global Temporary Tables?

1. Local Temp tables are prefixed with a single pound (#) symbol, whereas global temp tables are prefixed with 2 pounds (##) symbols.
2. SQL Server appends some random numbers at the end of the local temp table name where this is not done for global temp table names.
3. The Local temporary tables are only visible to that session of the Server which has created it, whereas Global temporary tables are visible to all the server sessions.
4. Local temporary tables are automatically dropped; when the session that created, the temporary tables are closed. Global temporary tables are also automatically dropped when the session that creates the temporary tables is closed.

Can you please explain when to use a temp table in SQL Server?

When we want to perform many operations on the data in a specific table in the database, we can load the data into a temporary table and then we can perform operations on the temporary table. Loading data into a temporary table speeds up the process because all the operations are performed locally on the client. Use of the Temporary Tables reduces the load on both the network and server as all the interaction with temporary tables occurs on the Client.

1.  When we are doing a large number of row manipulation in stored procedures.
2.  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.
3.  When we are having a complex join operation.
4.  A Temporary Table variable can be very useful when used with stored procedures to pass input/output parameters or to store the result of a table-valued function.

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 we maintain a persistent connection or if connection pooling is enabled, then it is better to explicitly drop the temporary tables we have created. However, it is generally considered a good coding practice to explicitly drop every temporary table we create.

In which database, the temporary tables get created?

TEMPDB database.

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

IF OBJECT_ID('tempdb..#LocalTempTable') IS NOT NULL
Begin
       Drop table #LocalTempTable
       Print 'Temporary table #LocalTempTable is deleted'
End
Else
Begin
       Print 'Temporary table #LocalTempTable is not Found,
              it may have been deleted already'
End

Usually, it is best to create a temp table through the create function. For example, if we want to check if that temp table existed and then drop/create a new one and insert data it would go something like this.

IF object_ID('tempdb..##temptable') IS NOT NULL
DROP TABLE ##TEMPTABLE
CREATE TABLE ##TEMPTABLE

INSERT INTO ##TEMPTABLE
SELECT e.EMPLOYEE
FROM Employee e
WHERE e.employeename = 'Pranaya'

This would check for the table and drop it if it exists then would create a new global temp table (indicated by the ##) and insert all employees with the name Pranaya into this temp table.

SQL appends random function as at the end of the Local table’s name. So, we can create a Local table with the same name. But the Global table’s name has to be unique. Why is that?

It is because a Local table is available to the connection that created it. Other users can have that name for their local table. But in the case of a global temporary table, it is available to all users on different sessions. So, its name cannot be different.

How to copy the structure of the temporary table from one table to another? i.e., copying the only structure and no data?

Select * into #Temp1 from tblEmployee
Select Top 0 * into #Temp2 from #Temp1
--Or
Select * into #Temp1 from tblEmployee where 1=2

What is the difference between a Temporary Table and a Table variable? 

1. A table variable is created in the memory whereas a temporary table is created in the TempDB. But if there is memory pressure, the pages belonging to a table variable may be pushed out to tempdb.
2. Table variables cannot be involved in transactions, logging or locking. This makes the table variable faster than a temporary table.
3. We can pass the table variable as a parameter to functions and stored procedures, whereas we cannot do the same with a temporary table.
4. The temporary table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variable can be faster, but if there are a lot of records, or there is a need to search the temporary table based on a clustered index, then a Temporary Table would be better. If we have less than 100 rows generally use a table variable. Otherwise, use a temporary table. This is because SQL Server won’t create statistics on table variables.

Both Temporary Tables (#Tables) and Table Variables (@Tables) in SQL Server provide a mechanism for Temporary holding/storage of the result-set for further processing

In the next article, I am going to discuss the most frequently asked SQL Server Indexes Interview Questions and Answers. Here, in this article, I try to explain most frequently asked SQL Server Temporary Tables Interview Questions and answers. I hope you enjoy this SQL Server Temporary Tables Interview Questions and answers article. I would like to have your feedback. Please post your feedback, question, or comments about this SQL Server Temporary Tables Interview Questions and answers article.