Category: Temp Tables & Variables

SQL, Temp Tables & Variables

Table Variables in SQL Server

Table variables are another types of temporary objects to store transient data. Please refer Temporary Table objects for more details about Temporary Tables in SQL Server.

Differences between Table Variables and Temporary tables

When do you use Temporary Tables over Table Variables, vice versa

So, now we should be good enough to take a decision on when do we need to use table variable and Temporary tables. I would like to reiterate statistics as one of the most important factor. Table variable does not have statistics where as temporary table has statistics maintained. As SQL Server optimizer is based on cost- based optimization, statistics are very important factor for Cost based approach to identify the best plan for your query. If your transient data is more than 100, I would suggest to use Temporary tables(with right indexes) over table variables to make use of the statistics to help SQL Server to identify the best plan for us. 


DECLARE @TabVariableTesting TABLE (
 id INT PRIMARY KEY,
 ScrambledData Varchar(7000)
)

INSERT INTO @TabVariableTesting 
SELECT number, Replicate('SQLZEalot',100)
 FROM master..spt_values WHERE NAME IS NULL

SELECT * FROM @TabVariableTesting WHERE id > 75


CREATE TABLE #TempTableTesting (
 id INT PRIMARY KEY,
 somecolumn Varchar(7000)
)

INSERT INTO #TempTableTesting
SELECT number, Replicate('SQLZEalot',100)
 FROM master..spt_values WHERE NAME IS NULL

SELECT * FROM #TempTableTesting WHERE id > 75

DROP TABLE #TempTableTesting

In other words, if you have very less data, then I would prefer to use table variables over temporary tables. If you need to store data in a user defined function, table variables are the way for you currently. The choice is not hard rule one, but choose the best for your needs/requirements.

Hope you enjoyed this post, please share your feedback and thoughts.

SQL, Temp Tables & Variables

Temporary objects in TEMPDB database

With this blog post, a continuation of earlier post on “TEMPDB – the most important system database in SQL Server“, we are going to see more about the temporary objects created in TEMPDB database. There are basically 3 different types of temporary objects that can create manually as below.

1. Local Temporary Tables

Local Temporary tables are created for operations that may require to access a specific set of data instead of querying every time the actual table. This will reduce the number of reads on the actual table and reduce the blocks/locks on the table.Everytime, you create a temp table (irrespective of the databases), the temp table is created in TEMPDB database.

Scope of the Local Temporary tables

The scope of the temp table is session/connection based, hence if you create two connections (two different management windows) and create the temp table with the same name, it will create two different objects in TEMPDB. 


open two Windows and execute the below
create Table #T1(Col1 int)
--Get the temp table created using the below
SELECT
    *
FROM
    sys.tables AS T
WHERE
    T.name LIKE '#t%' ;

However, as local temporary tables scope is session/connection based, these objects can still be used at nested levels.

But, wait, Will you be able to use temporary table in a function? – BIG NO. That is one of the difference between procedures and functions.

It is important to understand about temporary table caching in sql server

2. Global Temporary Tables

Global Temporary tables are again another temporary tables in SQL Server created in TEMPDB. Those are created by prefixing “##” the tablenames.

Scope of Global Temporary tables

Global Temporary tables are visible to all sessions once its created. On the flip side, there will be more contention if global temporary tables are heavily used. In addition, the scope of the global temporary table ends immediately when the session that created is terminated (or) any other session that uses the object is dropped manually. So, if you are planning to use global temporary tables, you should be very careful on its usage.

3. Temporary procedures

I believe, most of us are familiar with Temporary tables. But what about temporary stored procedures? Yes, just like temporary table, you can create temporary procedures as well in two ways – Local and Global. As we discussed earlier, the scope of the Local and Global procedures exactly same as temporary tables. These temporary tables are created in TEMPDB.

Let us look at the usages of temporary procedures. I could not find any good reason for using temporary procedures, may be, we have not used with this. But, it may be helpful while testing any procedure where we do not want to pollute the database with unnecessary objects. While deploying database, we may need to create temporary procedures for any purpose which we may not be using later etc.

If you have any use case, I would request to share your ideas.

SQL, Temp Tables & Variables

TEMPDB – the most important system database in SQL Server

Introduction

If you had anytime worked in SQL Server, you would be knowing about a system database – TEMPDB. TEMPDB is one of the most important system databases in SQL Server. As the name denotes TEMPDB database is a temporary database to SQL Server to do certain operations. As the topic is vast and its impossible to cover all aspects of TEMPDB in a single blog, planning to cover in multiple blogs to explain about TEMPDB.

Characteristics of TEMPDB

    1. TEMPDB is automatically created/recreated every time you start/restart your SQL Service That means, data anything reside in the tempdb is not persistent forever. If the service has been restarted, all the data will be lost.

    2. Without TEMPDB, you will not be able to start your SQL Server

    3. TEMPDB cannot be dropped or created manually

    4. TEMPDB cannot be backup up

    5. TEMPDB can have ONLY on SIMPLE recovery model

    6. TEMPDB can have only one filegroup(PRIMARY Filegroup)

    7. TEMPDB is a shared database

    8. TEMPDB never stored redo information, That means, you can ONLY rollback with help of undo information

    9. TEMPDB databse cannot be take to OFFLINE

    10. DBCC CHECKALLOC and CHECKCATALOG cannot be issued

Usages of TEMPDB

TEMPDB has been used in SQL Server for various purposes.

    1. Global/Temporary tables/Temporary procedures are created in TEMPDB (more details)

    2. Table Variable (more details)

    3. Cursors and internal objects created by DB engine to store intermediate results

    SQL Server database engine will create internal work tables as required as per the instruction from the relational engine. This is to improve the performance of operations like sorting, spooling, hashing etc.(more details)

    4. Online index operations needed TEMPDB

    5. Snapshot and Read Committed Snapshot Isolation requires TEMPDB

    6. Triggers

    7. Version store

I am sure this not an exhaustive list, If I miss anything, please feel free to add as comments.

How to understand the usages of TEMPDB 

select
reserved_MB=(unallocated_extent_page_count+
			version_store_reserved_page_count+
			user_object_reserved_page_count+
			internal_object_reserved_page_count+
			mixed_extent_page_count)*8/1024. ,
unallocated_extent_MB =unallocated_extent_page_count*8/1024., 
internal_object_reserved_page_count,
internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.,
user_object_reserved_page_count,
user_object_reserved_MB =user_object_reserved_page_count*8/1024.
from sys.dm_db_file_space_usage

See Also

Whats new with TempDB in SQL Server 2016

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL, Temp Tables & Variables

Temporary table caching in SQL Server

Caching of Temporary table is an interesting topic but not appreciated/recognized one in SQL Server due to several reasons.

First off, Let us see whats caching of temporary tables? – Caching temporary objects like temp tables/table variables will improve the performance of execution by eliminating the
recreation of objects.There are certain operations which will prevent the caching of temporary objects are below.

1. DDL commands once the object is created
2. Recompilation of Procedure associated
3. Dynamic SQL is used to create the objects
4. Named Constraints etc

Today, we are going to see how DDL commands is preventing the caching of temp objects.

Lets Create a Procedure and create a temp object:


--Create a procedure 
create Procedure Test_TempCaching
as
Begin
	create Table #Temp (EmpId int, FirstName varchar(50), LastName Varchar(50))
	create clustered index ix_test on #Temp(EmpId)
	Insert into #Temp Values(1,'SQL','Server'),(2, 'Biz','Talk')
End

We are going to see the temp tables creation rate using the below query:


SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'

The above will result a cumulative information on the number of times the temp objects created.

For the first time, when we create the procedure and executing the procedure, it will create a plan for the procedure and temp object is being created and cached.
If you look at the procedure, A temp table is created and later the object has been modified with DDL to add an index on the temp table.

Every time, the procedure gets executed, you can see the value gets changed,
Use the below query multiple times, and see the difference.


SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'
EXEC  Test_TempCaching
SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'


The above behavior shows that, if there is any DDL applied after the temp object, then the object will become obsolete and evict from the cache(later) and create a new object. You may find a performance issue if the procedure is executing very frequently in your application.

Now, Let us modify the procedure to comine the DDL command inline at the creation of temporary table as below:


ALTER Procedure Test_TempCaching
as
Begin
	create Table #Temp (EmpId int, FirstName varchar(50), LastName Varchar(50), Primary Key(EmpID))
	Insert into #Temp Values(1,'SQL','Server'),(2, 'Biz','Talk')

End

If you execute for the first time, you can execute the value gets changed. This is because, for the first time, the plan from the cache will be deleted once the procedure altered and a new plan will be created for the procedure. But for subsequent execution, the cntr_value never gets changed as the object has already been cached.


SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'
EXEC  Test_TempCaching
SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'

Conclusion:
If you see more high temp tables creation rate in your application, you may find the procedures and see if you can take advantage of the above scenario.
There is a caveat – statistics with temp objects(I am not covering here),you may need to verify and confirm the changes thoroughly to make sure you do not have any other performance issues.