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.

Temporary Tables In SQL Server










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. 

HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE 1


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.

HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE 2


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

Select Into Insert Into 1


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

Select Into Insert Into 2

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.

Select Into Insert Into 1







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 Insert Into 1



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

BACKUPBUFFER Wait Type In SQL Server

A lot of times the DBA will come across one wait type and that is the BACKUPBUFFER. The BACKUPBUFFER wait type will occur every time there is a backup happening in your SQL Server instance. It doesn’t matter if the backup operation is written on a tape mount or not, the SQL Server will still generate the BACKUPBUFFER wait type. Let’s look in detail at the BACKUPBUFFER wait type.

What is the BACKUPBUFFER Wait Type?

The BACKUPBUFFER wait type is generated whenever there is a backup operation happening in an SQL Server instance. When a backup runs, the SQL Server allocates a buffer for the backup process. The wait type will occur regardless of any backup method used. It can be a full, differential, or log backup. Once the buffer allocates, the buffer gets filled with data in your database and will be written to the backup file.

Additionally, the backup buffers with regards to amount and size are automatically calculated by SQL Server. However, the values can be configured manually by passing parameters to the backup or restore command.

 Backupbuffer Wait Type 1

How to check the backup buffer amount and size?

To check the backup buffer amount and size, we will first have to enable two trace flags, 3213 and 3605. These two trace flags will output backup and restore details into the SQL Server error log.

DBCC TRACEON (3213);
DBCC TRACEON (3605); 

Note: Enable these trace flags in SQL Server under a DBA’s guidance or Microsoft support. 

We now have an idea of how the backup operation pushes the information inside the SQL Server error log. Additionally, we also got the idea that the SQL Server backup process uses a buffer to store data in the backup file.

What we need to understand is, when the BACKUPBUFFER wait type occurs in an SQL Server instance? The BACKUPBUFFER wait type comes in whenever the buffers are not available to write directly in the backup file. The backup process will wait until the buffer is available. When the backup process waits, the BACKUPBUFFER wait type will keep generating. The wait type shows up until the buffer is directly available.

Sys.dm_os_wait_stats

You can check the wait type details in the sys.dm_os_wait_stats DMV. 

select * from sys.dm_os_wait_stats  where wait_type ='BACKUPBUFFER'

How to Lower BACKUPBUFFER Waits?

The BACKUPBUFFER wait type is not a concern of performance issues in an SQL Server instance. Whenever there is a backup operation running in your SQL Server machine, the BACKUPBUFFER wait type can come up. We can commonly lower the BACKUPBUFFER wait times by adding more buffers for the backup operation. This alteration can be done by specifying the BUFFERCOUNT in the backup command.

Note: Making a change in the BUFFERCOUNT can result in out-of-memory problems in case large buffers get generated. Use the option under a DBA’s guidance. 

Bottomline

The BACKUPBUFFER wait type occurs whenever a backup runs in an SQL Server instance. The value of this wait type can increase when the backup operation is waiting for more buffers to allocate. In most cases, the BACKUPBUFFER wait type is not a performance overhead. 

Difference Between sp_who and sp_who2 In SQL Server

The sp_who and sp_who2 are the two most used system stored procedure to get information on users, sessions, and processes of an instance in an SQL Server. In a production environment, it becomes an important aspect for a DBA to get the SQL Server processes running. Though both systems' stored procedures are used to get the users, sessions, and processes information, there are some key differences.

sp_who

  • The sp_who has limited columns to show. The results are all related to the processes running in an SQL Server instance.
  •  The sp_who is official and documented by Microsoft.  
  • The sp_who is supported by Microsoft.

sp_who SQL Server

Sp_who2

  • The sp_who2 has more columns to show than the sp_who. Like sp_who, the results are all related to the processes running in an SQL Server instance.
  •  The sp_who2 is not official and is documented by Microsoft.
  • The sp_who2 is not supported by Microsoft. 

sp_who2 SQL Server

Bottomline

Both sp_who and sp_who2 stored procedures are handy for a DBA to get the process information in SQL Server. For less column information you can prefer to go with sp_who whereas for more column information you can prefer to go with sp_who2. You can judge which stored procedure to use based on your requirement.