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.
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 the 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?
- Local Temporary Tables
- Global Temporary Tables
What is 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.
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 is Global Temporary Tables?
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.
-- Creating Global Temporary Table CREATE TABLE ##EmployeeDetails(Id Int, Name Varchar(50)) -- Insert some data Insert Into ##EmployeeDetails values (1,'Mike') Insert Into ##EmployeeDetails values (2,'John') -- To View the Global Temporary Tables data Select * from ##EmployeeDetails
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?
- Local Temp tables are prefixed with single pound (#) symbol, whereas global temp tables are prefixed with 2 pounds (##) symbols.
- 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.
- 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.
- 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 temp table?
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.
- When we are doing a 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.
- 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?
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?
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
Note: 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 Local table’s name. So, we can create a Local table with the same name. But 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 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
POINTS TO REMEMBER BEFORE USING TEMPORARY TABLES
- Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can cause performance issues.
- The number of rows and columns need to be as minimum as needed.
- Tables need to be deleted when they are done with their work.
What is the difference between a Temporary Table and a Table variable?
- A table variable is created in the memory whereas a temporary table is created in the TempDB. But if there is a memory pressure, the pages belonging to a table variable may be pushed out to tempdb.
- Table variables cannot be involved in transactions, logging or locking. This makes the table variable faster than a temporary table.
- We can pass the table variable as a parameter to functions and stored procedures, whereas we cannot do the same with a temporary table.
- 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 this article, I try to explain most frequently asked SQL Server Temporary Tables interview questions and answers. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.