Temporary table interview questions and answers

In this article I will share some frequently asked temporary table interview questions and answers in SQL server. 

1. What is Temporary table in sql server? Why we use temp table?

With temporary table you can store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables.

Use:

These tables can be created at run time and can do the all kinds of operations that one normal table can do.

More info.. http://www.codechef4u.com/post/2015/04/30/Temporary-table-in-sql-server

2. What is the Difference between a Local and a Global Temporary Table?

Scope:

Local:
 Local temporary tables are only available to the current connection to the database for the current user.
Global:
Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Syntax:

Hash (#) sign is used while creating local temporary table.
 Example:
CREATE TABLE #ProductDetails(
 ID int,
 Product nvarchar(100),
 Author nvarchar(100))
Double Hash (##) sign is used while creating local temporary table.
Example:
CREATE TABLE ##ProductDtlGlbl(
 ID int,
 Product nvarchar(100),
 Author nvarchar(100))

3. What is local temp table?

Local temporary tables are only available to the current connection to the database for the current user. They are automatically deleted when the user disconnects from instances.  

Hash (#) sign is used while creating local temporary table.

More info..http://www.codechef4u.com/post/2015/04/30/Temporary-table-in-sql-server

4. What is global temp table?

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Double Hash (##) sign is used while creating local temporary table.

http://www.codechef4u.com/post/2015/04/30/Temporary-table-in-sql-server

5. How to insert stored procedure result into Temporary Table?

1.   If Schema known by creating #temp table Schema.

2.  For Unknown Schema – Table Created at Runtime, Using OPENROWSET will get the job done, but it will incur some additional overhead for opening up local connections and marshaling data.

I prefer first solution over OPENROWSET due to overhead issue.  

More info… http://www.codechef4u.com/post/2015/06/28/How-to-insert-results-of-a-Stored-Procedure-into-a-Temporary-Table

6. Can you create foreign key constraints on temporary tables?

No, that is not allowed.

7. in which database, the temporary tables stored and get created?

Microsoft SQL Server Compact creates a temporary database (tempdb Database) for storing and creating temporary data such as temporary tables.

8. How Check if temp table exist and delete (drop) if it exists?

You can check if table exists or not in Temp DB, and if exits then use drop command for table deletion.

Example:

--check if table exits
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
--drop table
DROP TABLE #TempTable

9. Do you have to manually delete temporary tables?

Not required, temporary tables are automatically dropped, when the session that created the temporary tables is closed.

Note: If connection pooling is enabled or if you maintain a persistent connection then suggestion is drop temporary table explicitly.

10. What is the Difference between CTE (common table expression) and a Temporary Table?

Scope and Use:

Temporary Table:
 In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table.
CTE:
A Common Table Expression (CTE in SQL) is a temporary result set that, derived from query (T-SQL query in MS SQL Server) And defined within the execution scope of a Select, Insert, Update, Delete statement.

Syntax:

Temp Table:
Hash (#) and double (##) sign used while creating temporary table.
Example:
CREATE TABLE #ProductDetails(
 ID int,
 Product nvarchar(100),
 Author nvarchar(100))
 
CTE:
It is created by using t-sql WITH statement.
Syntax:
WITH
expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

Use in views and user defined functions:

1. You can’t use temp table in user defined function and views.
2. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.