Recently, I have been conducting many training sessions at a leading technology company in India. During the discussion of temp table and table variable, I quite commonly hear that Table Variables are stored in memory and Temp Tables are stored in TempDB. I would like to bust this misconception by suggesting following:
Temp Table and Table Variable — both are created in TempDB and not in memory.
Let us prove this concept by running the following T-SQL script.
/* Check the difference between Temp Table and Memory Tables */
-- Get Current Session ID
SELECT @@SPID AS Current_SessionID
-- Check the space usage in page files
SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = (SELECT @@SPID )
GO
-- Create Temp Table and insert three thousand rows
CREATEÂ TABLE #TempTable (Col1 INT)
INSERTÂ INTO #TempTable (Col1)
SELECTÂ TOP 3000Â ROW_NUMBER() OVER(ORDERÂ BY a.name)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the space usage in page files
SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = (SELECT @@SPID )
GO
-- Create Table Variable and insert three thousand rows
DECLARE @temp TABLE(Col1 INT)
INSERTÂ INTO @temp (Col1)
SELECTÂ TOP 3000Â ROW_NUMBER() OVER(ORDERÂ BY a.name)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the space usage in page files
SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = (SELECT @@SPID )
GO
-- Clean up
DROPÂ TABLE #TempTable
GO
Let us see the resultset. It is very clear that the size of the table variable and temp table is the same and created in TempDb.
Have you ever heard of this misconception? Do you know any other method to prove that both Temp Table and TableVariable are created in TempDB.
Reference: Pinal Dave (https://blog.sqlauthority.com)
68 Comments. Leave new
Hello,
In my case when I use table variable my query time counts 7 sec and for temp table it is near about 30 sec.
my main table rows are 3 million. After reading all these comments i am confuesd what should i use ?
according to this blog table variable consumes double size then temp table.
Plz make me happy with some usefull advice.
Thanks & Regards
Pramod sharma
Pinal dave, I have used Global temp table and temp table.
Temp table has dropped once the query get closed,
Global temp table has dropped once the connection where it created get closed but in your definition you have mentioned that the global temp table definition will remain permanently, how ? kindly give me explaination..
If memory serves me correctly, it’s not about in-memory or not; it’s all about scope of the connection. #TableName will get you in trouble with web services that use one connection. However, if you use table variables, the temp table is contained within the scope of the stored procedure.
First, the table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb. Here is one example shows space taken by table variable in tempdb
use tempdb
go
drop table #tv_source
go
create table #tv_source(c1 int, c2 char(8000))
go
declare @i int
select @i = 0
while (@i 100 and name like ‘c%’
This will return two rows containing columns c111 and c222. Now this means that if you were encountering DDL contention, you cannot address it by changing a #table to table variable.
· Third, transactional and locking semantics. Table variables don’t participate in transactions or locking. Here is one example
— create a source table
create table tv_source(c1 int, c2 char(100))
go
declare @i int
select @i = 0
while (@i < 100)
begin
insert into tv_source values (@i, replicate ('a', 100))
select @i = @i + 1
end
— using #table
create table #tv_target (c11 int, c22 char(100))
go
BEGIN TRAN
INSERT INTO #tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
— using table variable
DECLARE @tv_target TABLE (c11 int, c22 char(100))
BEGIN TRAN
INSERT INTO @tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
— Now if I look at the locks, you will see that only
— #table takes locks. Here is the query that used
— to check the locks
select
t1.request_session_id as spid,
t1.resource_type as type,
t1.resource_database_id as dbid,
(case resource_type
WHEN 'OBJECT' then object_name(t1.resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
t1.resource_description as description,
t1.request_mode as mode,
t1.request_status as status,
t2.blocking_session_id
from sys.dm_tran_locks as t1 left outer join sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address
Another interesting aspect is that if I rollback the transaction involving the table variable earlier, the data in the table variable is not rolled back.
Rollback
— this query will return 100 for table variable but 0 for #table.
SELECT COUNT(*) FROM @tv_target
· Fourth, the operations done on table variable are not logged. Here is the example I tried
— create a table variable, insert bunch of rows and update
DECLARE @tv_target TABLE (c11 int, c22 char(100))
INSERT INTO @tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
— update all the rows
update @tv_target set c22 = replicate ('b', 100)
— look at the top 10 log records. I get no records for this case
select top 10 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%tv_target%'
order by [Log Record Length] Desc
— create a local temptable
drop table #tv_target
go
create table #tv_target (c11 int, c22 char(100))
go
INSERT INTO #tv_target (c11, c22)
SELECT c1, c2
FROM tv_source
— update all the rows
update #tv_target set c22 = replicate ('b', 100)
— look at the log records. Here I get 100 log records for update
select operation,context, [log record fixed length], [log record length], AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%tv_target%'
order by [Log Record Length] Desc
· Fifth, no DDL is allowed on table variables. So if you have a large rowset which needs to be queried often, you may want to use #table when possible so that you can create appropriate indexes. You can get around this by creating unique constraints when declaring table variable.
· Finally, no statistics is maintained on table variable which means that any changes in data impacting table variable will not cause recompilation of queries accessing table variable.
Hello,
please,How can I programaticaly get the structure of Table variable ?.
i can get the name from sys.type but not the metada.
need help.
@said,
Table Variable has to be defined and declared, unlike a temporary table.
You cannot get a structure into a Table variable unlike temporary table.
Simple example to declare a table variable.
Declare @Example Table (Eid int NOT NULL, EmpName varchar(40) NOT NULL)
There is always an option to go dynamic and build a table variable by reading the source table structure, but I dont recommend it unless it is absolutely neccessary. If the table structure does not change, then declare the table variable as shown above.
Hope this helps.
~ IM.
Hi Pinal,
I ahd seen Ramdas example2, Is it possible to use temp table inside of a function?
Regards,
Sukumar
you can create table variable even if you dont have an acces on tempdb
but u cant create temp table.
so that proves temp tables are created in tempdb but table variables are not
ALTER PROC usp_InsertCustomer
(@CustName VARCHAR(50),
@dob DATETIME,
@Gender CHAR(1),
@Contact VARCHAR(10),
@Pan VARCHAR(10),
@Add VARCHAR(100)
)
AS
BEGIN
BEGIN TRY
BEGIN
IF EXISTS(SELECT * FROM tbl_Customer1)
BEGIN
DECLARE @custid VARCHAR(4)
DECLARE @count INT
–Auto generating customer id
SET @count = (SELECT COUNT(CustId) FROM tbl_Customer1)
SET @custid = ‘C’ + CONVERT(VARCHAR(4),100+@count)
SELECT @custid
–Auto generating Customer table name
DECLARE @TranTable VARCHAR(20)
SET @TranTable=’Tran_Table’+@custid
SELECT @TranTable
DECLARE @abc VARCHAR(100)
SET @abc=’CREATE TABLE @TranTable(TID VARCHAR(10) PRIMARY KEY,
TranType VARCHAR(20) NOT NULL,
TranDesc VARCHAR(100) NOT NULL,
Amount MONEY NOT NULL,
Tag VARCHAR(20) NOT NULL,
TranDate DATETIME NOT NULL
)’
SET @abc=REPLACE(@abc,’@TranTable’,@TranTable)
EXEC(@abc)
–Inserting values into customer table
–Inserting values into Customer login Table
DECLARE @uid VARCHAR(50)
DECLARE @pwd VARCHAR(50)
SET @uid=@CustName+@count
SET @pwd=@CustName+@count
INSERT INTO tbl_CustLogin VALUES(@custid,@uid,@pwd,GETDATE())
–On successful Insertion returning 0
RETURN 0
END
ELSE
–If their is no customer insert bydefault 1 customer id
INSERT INTO tbl_Customer1(CustId) VALUES(‘C100’)
RETURN -1
END
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
RETURN -2
END CATCH
END
Sir in above code i want to create permanant table is it possible because every time their will be new name as per my table columns
Hi,
I have one query over here, when i created Temp Table and insert records, the user_objects_alloc_page_count comes out to be 10, when i drop the table, again it shows 10 against user_objects_alloc_page_count. It should come back to 4 as it was initially …please throw light on this..
Temp tables cannot be used in table valued functions where table variables can be used
Temp tables may have indexes added and table variables only can have Primary and unique key constraints as indexes.
Table variables are dropped at the end of a batch but temp tables are dropped at the end of a session / stored procedure
Table variables are not transactional and do not roll back. temp tables are transactional and do roll back.
Temp tables are visible to called procedures but table variables
Query optimizer always assumes that one row is returned from a table variable. Temp tables with indexes have statistics that generate better plans.
Try the following code which shows a new temp table is created every time when the code block is executed
BEGIN
— Create Table Variable and insert three thousand rows
DECLARE @temp TABLE(Col1 INT)
INSERT INTO @temp (Col1)
SELECT TOP 3000 ROW_NUMBER() OVER(ORDER BY a.name)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
SELECT * FROM tempdb.sys.objects order by create_date desc
END
My mind has been blown. Thank you for the article!
Hi Pinal,
Can you correct the heading of your article which says “Temp table in memory a myth”….It should be “Table Variable in memory a myth”
Hi Pinal,
If I have created one temporary table #table1 in one stored procedure in sql having some input variables.
If multiple user executes this then is #table1 created twice time in TempDB or created only once.
If created only once, then can user1 get data of #table1 in which data is inserted by other user.
Please have some reply.
Can we add index on temp table ‘#testtable’?
Thanks
Yes you can add indcies to the temp tables
yes
Hi Pinal,
Can you Please tell me , in a distributed environment if I use temp table , am I going to face any performance issue.
Hi Pinal,
On which basis we will distinguish Large Table and Small Table.
Can I say transaction table as Large table and master table is small table.Please Clarify . Thanks.
Hi Pinal,
Let I am creating a Payroll module which is supposed to generate pay slip for 50 Thousands employees at a time and In my Proc I am dealing with temp table ,which is not good by some experts view ,so please help me how should I deal ?
I think , I should adopt table partitioning concept on main transaction table and based on that i will go forward in my GUI.Is it Fine or what step should I adopt.
Please Clarify ASAP . Thanks.