21/05/2021
Why TempDB?
The TempDB system database plays an important role in SQL Server performance tuning process. Because it is used as caching storage to store different types of user database objects and to store the system internal objects in order to speed up the SQL Server Database Engine related processes.
Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb.
I want to try give an introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it.
The temdb is a special system database used to store temporary objects and data like tables, views table variables, tables returned in functions, temporary objects and indexes. The temdb can also be used for internal operations like rebuilding indexes (when the SORT_IN_TEMPDB is ON), queries using UNION, DBCC checks, GROUP BY, ORDER BY. Hash join and Hash aggregate operations.
But you must know,”The tempdb is in simple recovery model because the information stored is temporary.”
Creating temporary objects:
There are several temporary objects in SQL Server. Let’s start with the local temporary table.
Local temporary tables:
The following example creates a temporary local table in the tempdb database with the information of the Person.Address table.
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[SpatialLocation]
,[rowguid]
,[ModifiedDate]
into
FROM [Person].[Address]
If you check the tempdb, you will find the table in the temporary table folder.
The prefix # is used to indicate that it is a local temporary table. You can access to the temporary tables from any database. These tables or objects created are visible only in the session where they were created.
Global temporary tables:
These tables are global and can be accessed from other sessions which was not in possible in local temporary tables.
The syntax is similar than the local temporary tables, but with double # #:
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[SpatialLocation]
,[rowguid]
,[ModifiedDate]
into #
FROM [Person].[Address]
Now Have a look on table type variables:
The table variables can be used instead of the global and local temporary tables. They are easier to handle and to dispose the resources, which makes the use more efficient. The following example will show how to create the table variable, how to insert data on it and how to show the variable values. The use is similar to a simple table. The following example shows how to declare a table variable, how to insert data and how to do a select:
DECLARE TABLE (id INT, col1 varchar(20))
insert into values(1,'First value'),(2,'Second value')
select * from
You can also create it for globally DECLARE @ TABLE (id INT, col1 varchar(20))
As you can see, it is very easy to use them like any other variable. In general, the local temporary tables are more efficient. The table variables are also stored in the tempdb. However, depending on the scenarios you can only use table variables or local temporary tables. For example, if you need indexes, you can only do it with a local temporary table. By the other hand, in a function, you can only use a table variable. Note that the statistics are not maintained in table variables. In routines, table variables require fewer compiles.
A special type of table variable is the Table-value parameter. This is very useful for client applications.
The following example shows how to create and use Table-value parameters:
CREATE TYPE Table_value AS TABLE
(id int
, name varchar(30) );
GO
We will also create a simple table to fill with the Table-value parameter in a stored procedure:
CREATE table product
(id int
, productname varchar(30))
The stored procedure will load the data from the Table-value parameter into the product table created.
CREATE PROCEDURE insertdata
table_value READONLY
AS
SET NOCOUNT ON
INSERT INTO product
([id]
,productname)
SELECT id,name
FROM ;
GO
Now, we are going to declare the Table-value parameter insert data there and call the stored procedure and do a select in the table to make sure that the data was inserted:
DECLARE
AS Table_value;
INSERT INTO (id, name) values
(1,'Camera'),
(2,'iPad');
EXEC insertdata ;
GO
select * from product
If everything is OK, you should be able to see the new data inserted in the products table using the stored procedure.
Nest Software
