SQL Temporary Table | Temp Table | Global vs Local Temp Table

Temporary tables in SQL Server also known as temp tables. They are used by database developers and DBAs to store data temporarily and work on this data for faster performance. Temporary tables provide temporary data storage in the exact form of original tables for quick access to data.

Temporary tables are stored in TempDB. They work like a regular table in that you can perform the operations select, insert, and delete as for a regular table. A temporary table is a base table that is not stored in the database but instead exists only while the database session in which it was created is active. At first glance, this may sound like a view, but views and temporary tables are rather different.

Please watch the attached video for all the SQL Temp Table Interview Questions live demo. You can download the SQL script from the bottom of the page.

In the above video, I have covered the following Temp Table Interview Questions with a live demo.

Q01. What is a Temp Table or Temporary Table in SQL? 

A temporary table or temp table in SQL is a table that exists temporarily in a database. It is created and populated on-the-fly and automatically cleaned up when the session or connection ends. Temporary tables are useful for storing data that you work with multiple times in a session but the data is not needed permanently.

Q02. Is a duplicate Temp Table name allowed?

Yes, the duplicate table name is allowed in the case of the local temp table only. But behind the scenes, SQL appends the random characters for the local temp tables.

It is possible to create multiple temporary tables with the same name in SQL Server. Each session or connection will have its own copy of the temporary table, so there will be no conflict between them.

Q03. Can a Temp Table be used for SELECT INTO or INSERT EXEC statement? 

Yes, you can use a temporary table for SELECT INTO or INSERT EXEC statements in SQL Server.

Q04. What are the different ways to create a Temp Table in SQL?

There are two ways to create a temporary table in SQL Server: using SELECT INTO or CREATE TABLE.

Once you have created the temporary table with the Create statement, you can insert data into it

using INSERT INTO statement

Q05. What is the difference between Local and Global Temporary Table in SQL?

In SQL Server, there are two types of temporary tables: local and global.

Local temporary tables are created using a single pound (#) symbol as a prefix to the table name. They are visible only to the connection that creates them and are deleted when the connection is closed.

Global temporary tables are created using two-pound (##) symbols as a prefix to the table name. They are visible to all SQL Server sessions and are deleted when all connections that have referenced them have closed.

Q06. What is the storage location for the Temp Tables?

Temporary tables are stored in the tempdb database in SQL Server. When you create a temporary table, SQL Server adds some additional characters to its name to provide a unique system name for it, and then it stores it in the sysobjects table in the tempdb database.

Q07. What is the difference between a Temp Table and a Derived Table in SQL?

A temporary table or temp table in SQL is a table that exists temporarily in a database. It is created and populated on-the-fly and automatically cleaned up when the session or connection ends.

On the other hand, a derived table is a virtual table that is created on the fly as a result of a subquery in the FROM clause of a SQL statement. It is not stored in the database and exists only for the duration of the query execution.

The main difference between a temporary table and a derived table is that a temporary table is a physical table that is stored in the database, whereas a derived table is a virtual table that is created on the fly and does not exist in the database.

Q08. What is the difference between a Temp Table and a Common Table Expression in SQL?

A temporary table or temp table in SQL is a table that exists temporarily in a database. It is created and populated on-the-fly and automatically cleaned up when the session or connection ends.

On the other hand, a common table expression or CTE is a named temporary result set that is defined within the execution scope of a single SQL statement. It is not stored in the database and exists only for the duration of the query execution.

The main difference between a temporary table and a CTE is that a temporary table is a physical table that is stored in the database, whereas a CTE is a virtual table that is created on the fly and does not exist in the database.

CTEs are useful for simplifying complex queries and breaking them down into smaller, more manageable pieces. They are created using the WITH keyword followed by a subquery that defines the result set.

Q09. How many Temp Tables can be created with the same name?

In SQL Server, it is possible to create multiple local temp tables with the same name. Each session or connection will have its own copy of the temporary table, so there will be no conflict between them.

Temporary tables are stored in the tempdb database in SQL Server. When you create a temporary table, SQL Server adds some additional characters to its name to provide a unique system name for it, and then it stores it in the sysobjects table in the tempdb database.

However, you can not create a duplicate global temp table because SQL does not append any random character to the global temp table name.

Q10. How many users or who can access the Temp Tables?

In SQL Server, local temporary tables are only available to the SQL Server session or connection that created them. They are automatically deleted when the session that created them ends. 

Global temporary tables, on the other hand, are visible to all SQL Server sessions and are deleted when all connections that have referenced them have closed.

Q11. Can you create an Index and Constraints on the Temp Table?

Yes, you can create indexes and constraints on temporary tables in SQL Server. Temporary tables support adding clustered indexes and non-clustered indexes after the table creation and implicitly by defining the Primary Key Constraint or Unique Key Constraint during the table creation

Q12. Can you apply Foreign Key constraints to a temporary table? 

In SQL Server, it is not possible to create foreign key constraints on temporary tables. 

The reason for this is that temporary tables are created in the tempdb database and are only visible to the SQL Server session or connection that created them. Since foreign key constraints are enforced across tables, it is not possible to create a foreign key constraint between a temporary table and a permanent table.

Q13. Can you use the Temp Table before declaring it?

Yes, In SQL Server, it is possible to create a temporary table without declaring it first using the SELECT INTO statement. You can then use the temporary table in subsequent queries without declaring it first.

This statement creates a temporary table and populates it with the results of the SELECT statement. The columns in the temporary table are automatically created based on the columns in the SELECT statement.

Q14. Can you use the Temp Table in the User-Defined Function (UDF)?

In SQL Server, it is not possible to use temporary tables inside a user-defined function. The reason for this is that temporary tables are created in the tempdb database and are only visible to the SQL Server session or connection that created them. 

Since user-defined functions are executed in a separate session, they cannot access temporary tables created in another session. However, you can use table variables instead of temporary tables inside a user-defined function. Table variables are created in memory and are only visible to the current session.

Q15. If you perform an Insert, Update, or delete operation on the Temp Table, does it also affect the underlying base table?

No. In SQL Server, temporary tables are stored in the tempdb database and are only visible to the SQL Server session or connection that created them. Therefore, any changes made to a temporary table will not affect the underlying base table.

Q16. Can you TRUNCATE the temp table? 

Yes, you can use the TRUNCATE TABLE statement to remove all rows from a temporary table in SQL Server.

Q17. Can you insert the IDENTITY Column value in the temp table? Can you reset the IDENTITY Column of the temp table? 

Yes, you can insert the IDENTITY column value in a temporary table in SQL Server using the IDENTITY function. The IDENTITY function generates a unique value for each row in the temporary table. 

You can also reset the IDENTITY column of a temporary table using the TRUNCATE TABLE statement.

Q18. Is it mandatory to drop the Temp Tables after use? How can you drop the temp table in a stored procedure that returns data from the temp table itself?

It is not mandatory to drop temporary tables after use in SQL Server. However, it is a good practice to drop them when they are no longer needed to free up system resources. You can drop a temporary table in SQL Server using the DROP TABLE statement.

If you want to drop a temporary table that is used in a stored procedure that returns data from the table itself, you can use a table variable instead of a temporary table. Table variables are created in memory and are only visible to the current session.

Q19. Can you create a new temp table with the same name after dropping the temp table within a stored procedure?

No, it is possible to create a new temporary table with the same name after dropping the temporary table within a stored procedure in SQL Server. 

Q20. Is there any transaction log created for the operations performed on the Temp Table?

SQL Server keeps a transaction log for each database, including the tempdb database. The transaction log records all modifications made to the database, including temporary tables. However, the transaction log is used for recovery purposes only and does not affect the behavior of temporary tables.

Q21. Can you use explicit transactions on the Temp Table? Does the Temp Table hold a lock? Does a temp table create Magic Tables?

Yes, you can use explicit transactions on temporary tables in SQL Server. Explicit transactions are transactions that start with a BEGIN TRANSACTION statement and end with either a COMMIT or ROLLBACK statement. They allow you to group a set of SQL statements into a single transaction that either completes or fails as a single unit

Q22. Can a trigger access the temp tables?

Yes, a trigger can access temporary tables in SQL Server. Temporary tables are stored in the tempdb database and are visible to the SQL Server session or connection that created them.

When a trigger is fired, it runs in the same session as the statement that caused the trigger to fire. Therefore, the trigger can access any temporary tables that were created in the same session.

Q23. Can you access a temp table created by a stored procedure in the same connection after executing the stored procedure?

In SQL Server, a local temporary table created in a stored procedure is dropped automatically when the stored procedure ends. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. However, the table cannot be referenced by the process that called the stored procedure that created the table.

Therefore, if you create a temporary table inside a stored procedure, you can access it within the same connection as long as you are executing nested stored procedures that reference the temporary table. However, you cannot access the temporary table from the process that called the stored procedure that created the table.

Q24. Can a nested stored procedure access the temp table created by the parent stored procedure?

Yes, a nested stored procedure can access the temporary table created by the parent stored procedure in SQL Server.

Q25. Can you ALTER the temp table? Can you partition a temp table?

Yes, you can alter a temporary table in SQL Server using the ALTER TABLE statement.

Q26. Which collation will be used in the case of Temp Table, the database on which it is executing, or temp DB? What is a collation conflict error and how you can resolve it?

When creating a temporary table, SQL Server will inherit the collation for the temporary table from the SQL Server instance default. However, you can use the COLLATE keyword to specify the collation for each column in the temporary table.

A collation conflict error occurs when two columns with different collations are compared or concatenated in a SQL Server query. To resolve this error, you can use the COLLATE keyword to specify the collation for one of the columns to match the other.

Q27. What is a Contained Database? How does it affect the Temp Table in SQL?

A contained database is a database that is self-contained and does not have any dependencies on the instance of SQL Server where it is installed. It contains all the necessary metadata and user information to define the database and its objects.

In a contained database, temporary table data is collated in the collation of the contained database. In non-contained databases, temporary table data is collated in the collation of tempdb.

Q28. Can you create a column with user-defined data types (UDDT) in the temp table?

Yes, you can create a column with user-defined data types (UDDT) in a temp table. However, there are some limitations to this approach. The UDDTs are not stored in tempdb, which is where temp tables are stored. Therefore, you cannot use a UDDT as a column type of a temp table directly.

However, there is a workaround that involves creating a new type in tempdb that is based on the UDDT. You can then use this new type as the column type of the temp table. 

Q29. How many concurrent users can access a stored procedure that uses a temp table?

The local temporary tables prefixed with # are kept on a per-session basis. Therefore, independent instances of the temporary table will be created per connection, and there will be no concurrency issues. 

However, if you’re using a global temporary table (##temptable), it is shared among all users connected to the server instance and can cause concurrency issues.

Q30. Can you pass a temp table to the stored procedure as a parameter?

No, you can not pass a temp table directly to the stored procedure. For this, you need to create a table-valued type in SQL.

NOTE: Please click here to download the SQL Script which is used in the video.

Recommended Articles

Most Frequently Asked SQL Interview Questions
Most Frequently Asked C# Interview Questions
Foreign Key Interview Questions
Unique Key Interview Questions
Primary Key Interview Questions
Derived Table (DT) Interview Questions
Unicode vs Non-Unicode Data Types Interview Questions
Table Variable (TV) Interview Questions
Table-Valued Type (TVT) Interview Questions
Common Table Expression(CTE) Interview Questions
Temp Table Interview Questions
Static Class Interview Questions and Answers
Sealed Class Interview Questions and Answers
Sealed Method Interview Questions and Answers
Abstract Class vs Interface
Interface Interview Questions and Answers

Thanks for visiting this page. Please follow and join us on  LinkedIn,  Facebook,  Telegram,  Quora,  YouTube,  Twitter,  Instagram, WhatsApp, VK,  Tumbler, and Pinterest for regular updates.