What Is A Temporary Table In SQL Server?

Temporary tables are commonly used by database developers and DBAs to store data temporarily. Temporary tables are also known as temp tables in SQL Server. We will be representing as temp table moving forward in this article. Temp tables are generally used for faster data retrieval and increased query performance. Let’s look deeper and learn about the temp tables in SQL Server.

What is a temp table in SQL Server?

The temp table is used to store data temporarily for faster data retrieval. It works like a normal table to perform select, insert, update, or delete commands. Temp tables are created with a prefix with either a single # or a double ##. Additionally, temp tables are automatically dropped when a session is closed in SQL Server.

Types of temp tables

There are two types of temp tables. Local temp table, and a global temp table.

Local temp tables

Local temp tables are created with a single # prefix on a table name. It means that the local temp tables are active and usable to the session that created them. No other user can access the temp table when the session is active upon creating a local temp table. However, if you are creating a local temp table within a stored procedure, the temp table will be dropped automatically.

Creating a temp table and working with DDL/DML commands.

create table #Temp_Table
(
[id] int,
[Hospital] varchar(100),
[Location] varchar(100)
)

insert into #Temp_Table
select 1, 'Apollo', 'New York'

Update #Temp_Table set Hospital = 'Apollo NY' where ID=1

Delete from #Temp_Table where ID=1

select * into #Temp_Table_2 from #Temp_table

drop table #Temp_table

Global temp table

Global temp tables are created with a double ## prefix on a table name. It means that the temp table is active and usable for other users as well. But the only protocol to follow is that the session that opened for creating the global temp table must be open and active for others to access. The moment the master session closes, the global temp table will be dropped, and other users lose access to the global temp table. 

create table ##Temp_Table
(
[id] int,
[Hospital] varchar(100),
[Location] varchar(100)
)

insert into ##Temp_Table
select 1, 'Apollo', 'New York'

Update ##Temp_Table set Hospital = 'Apollo NY' where ID=1

Delete from ##Temp_Table where ID=1

select * into ##Temp_Table_2 from ##Temp_table

drop table ##Temp_table

Where are the temp tables stored in SQL Server?

All the temp tables are stored in the tempdb. All the temp tables sit in the temporary tables folder under the tempdb.

Bottomline

Temporary tables are used for faster data retrieval. In many cases, temp tables come handy while optimizing a query. Both local and global temp tables will be stored in the tempdb. 

You may also refer: 
CTE in SQL server

HADR_LOGCAPTURE_WAIT And HADR_WORK_QUEUE Wait Type In SQL Server

The HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE are waits related to AlwaysOn Availability Group. The AlwaysOn Availability Group concept was introduced in SQL Server 2012. It was introduced to replace database mirroring. The AlwaysOn wait types can be easily recognized by the HADR_ prefix. We will be looking into the HADR_LOGCAPTURE_WAIT and HADR_WORKQUEUE waits in detail.

What are the HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE wait types?

The HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE wait types are benign and occur naturally within the AlwaysOn configuration.

HADR_LOGCAPTURE_WAIT

HADR_LOGCAPTURE_WAIT occurs when waiting for the log records to be available. We can encounter this wait type when new log records are generated by connections. Additionally, the wait type can be expected when the log scan is read from the disk. HADR_LOGCAPTURE_WAIT occurs in the primary database inside the AlwaysOn Availability Group. 

HADR_WORK_QUEUE

The HADR_WORK_QUEUE wait type occurs when the AlwaysOn Availability Group is waiting for a new worker thread to be assigned. This is an expected action by the AlwaysOn functionality. Additionally, the wait type is an indication of free threads waiting for work. 

Sys.dm_os_wait_stats

You can check the HADR_LOGCAPTURE_WAIT and HARD_WORK_QUEUE wait type information from the DMV – sys.dm_os_wait_stats.

Bottomline

Both HADR_LOGCAPTURE_WAIT and HARD_WORK_QUEUE wait types are good. They are natural to encounter if we have the AlwaysOn enabled servers. Having these wait types does not indicate a performance overhead. There is no attention needed in case you see these wait types.

More Wait Types For your reference:

What is REDO_THREAD_PENDING_WORK Wait Type?
What is HADR_SYNC_COMMIT Wait Type?

SELECT INTO TEMP TABLE Vs INSERT INTO TEMP TABLE Statement In SQL Server

If you are a database developer or admin, then the SELECT INTO TEMP TABLE and INSERT INTO TEMP TABLE will be almost your everyday task. This article will help you explore the SELECT INTO and INSERT INTO TEMP TABLE in detail.

The SELECT INTO TEMP TABLE is a simple method of creating a new table and copying the data from the source table. The SELECT INTO TEMP TABLE does the following operations internally.

• Creates a new table like the source table with the exact same column with data type
• Reads and inserts to the new table from the source table

SELECT INTO TEMP TABLE Statement

The SELECT INTO TEMP TABLE is used to create a new table and copy the data over to either a user table or a temporary table.

select * into #Tmp_Employees from employee

The above example will insert all records into the #Tmp_Employees temporary table from the employee user table.

Suppose we want to insert specific columns of the employees' table into the temporary table, then we must specify the column names in the SELECT INTO statement.

select empno,ename, job into #Tmp_Employees from employee

In both the examples of select into with * and with specific columns, the column name remains the same as the source table.

INSERT INTO TEMP TABLE Statement

Again, the INSERT INTO TEMP TABLE statement is used to insert the source table data into the temporary table. In this case, the table creation will have to be manually created by the user. The SQL Server does not involve creating the temporary table internally.

INSERT INTO statement can be used either for inserting into an existing user table, or a newly created temporary table.

The below example illustrates the insertion of all columns into a temporary table from the employee table.

create table #Tmp_Employees
(
empno int,
ename varchar(100),
job varchar(100),
deptno smallint,
comm smallint,
ID int
)

insert into #Tmp_Employees
select * from employee

The statement selects all columns with the help of a * from the employee table and inserts it into #Tmp_Employees temporary table.

The below example illustrates the insertion of particular columns into a temporary table from the employee table.

insert into #Tmp_Employees (empno, ename, job)
select empno, ename, job from employee

The statement selects just empno, ename, job columns from employee table and inserts them into #Tmp_Employees temporary table. 

SELECT INTO Vs INSERT INTO TEMP TABLE Statement

• SELECT INTO creates a destination temporary table automatically. It reads data from the source table and inserts it into the temporary table.
• INSERT INTO doesn’t create a destination temporary table automatically. We have to explicitly insert it into the existing user table or a temporary table from the source table.

Performance Metrics

Starting from SQL Server 2014, the SELECT INTO performance has shown better. This is because the statements have been running parallel to improve performance. However, through the course of my experience, here is my take below.

SELECT INTO works well if the columns to create are lesser. If the number of columns increases with complex logic underlying, the SELECT INTO performance degrades.

INSERT INTO works well if the columns to be inserted are more.

In general, the SELECT INTO performance is better than the INSERT INTO. Be sure of the number of columns you are supplying in both SELECT INTO and INSERT INTO statements. 

Bottomline

In this article, we have learned about the difference between the SELECT INTO and INSERT INTO statements. In a practical environment, both methods are extensively used based on the scenario. Use them in appropriate conditions to get the best results or performance. 

More articles for your reference:
Difference Between Shared Lock, Exclusive Lock And Update Lock In SQL Sever
Difference Between Truncate and Delete Command in SQL Server