Few days ago I wrote an article on the myth of table variable stored in the memory—it was very well received by the community. Read complete article here: SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth.
Today, I am going to write an article which follows the same series; in this, we will continue talking about the difference between TempTable and TableVariable. Both have the same structure and are stored in the database — in this article, we observe the effect of the transaction on the both the objects.
DECLARE @intVar INT
SET @intVar = 1
SELECT @intVar BeforeTransaction
BEGIN TRAN
SET @intVar = 2
ROLLBACK
SELECT @intVar AfterRollBackTran
It is a very well known fact that variables are unaffected by transaction as their scope is very limited, and for the same reason, variables should be very carefully used. Let us see very a quick example below that demonstrates that there transactions do not affect the local variable.
Now let us carry out the same test on TempTable and Table Variables. If Table Variables are true variables, they should also demonstrate the same behavior. See the following example:
USE AdventureWorks
GO
-- Create Temp Table and insert single row
CREATE TABLE #TempTable (Col1 VARCHAR(100))
INSERT INTO #TempTable (Col1)
VALUES('Temp Table - Outside Tran');
-- Create Table Variable and insert single row
DECLARE @TableVar TABLE(Col1 VARCHAR(100))
INSERT INTO @TableVar (Col1)
VALUES('Table Var - Outside Tran');
-- Check the Values in tables
SELECT Col1 AS TempTable_BeforeTransaction
FROM #TempTable;
SELECT Col1 AS TableVar_BeforeTransaction
FROM @TableVar;
/*
Insert additional row in trans
Rollback Transaction at the end
*/
BEGIN TRAN
-- Insert single row
INSERT INTO #TempTable (Col1)
VALUES('Temp Table - Inside Tran');
-- Insert single row
INSERT INTO @TableVar (Col1)
VALUES('Table Var - Inside Tran');
ROLLBACK
-- Check the Values in tables
SELECT Col1 AS TempTable_AfterTransaction
FROM #TempTable;
SELECT Col1 AS TableVar_AfterTransaction
FROM @TableVar;
GO
-- Clean up
DROP TABLE #TempTable
GO
It is clear from example that just like any local variable table variable is not affected from transaction. This is very important detail to note as I have quite often seen developer using TempTable and TableVariables interchangeably without understanding their effect on transaction.
Let me know if you have any other tip which you think will be helpful to readers.
Reference: Pinal Dave (https://blog.sqlauthority.com)
34 Comments. Leave new
Hi pinal,
Since table variables are unaffected by transaction, does this means that they bypass completely the tempdb log?
The reason why I am asking is, since I make very heavy use of memory tables, so my question is if putting the tempdb log file on its own array will improve performance?
I have conducted an experiment to see it uses tempdb’s log.
On a SQL Server instance that I am the sole user of it, I have first restarted SQL Server, and I have checked the tempdb log which was at that time 512 kb.
Then I have executed the following query against one of the databases on the instance:
DECLARE @i int
DECLARE @a table (i int)
SET @i = 0
WHILE @i < 100000
BEGIN
INSERT INTO @a values(@i)
SET @i = @i + 1
END
After which I have checked again the log file size and it was now 1536 kb.
So this shows us, that although table variables are not being rolled back, and although the log of tempdb is not used to redo anything anyway (since at startup tempdb is recreated), still any write to table variables is being logged to tempdb's log.
And this is something that deems to be unnecessary, especially due to the fact that SQL Server has to for writes to the log, and if SQL Server would not write table variables tot he log then there would probably be a performance gain.
As such I would say that for an OLAP server, I would rather have an extra spindle for the tempdb log rather then for the database log, since for reads the tempdb log perfomance is more of a concern.
(the last answer) claims that table variables are not being written to the log, and this is the reason why they are not participating in transactions, however as I have showed this appears to be wrong.
Thanks
Hi
I am following your articles from quite a while and honestly learn alot from examples you always gave in your articles. I have a situation in which I am passing table name as a parameter and want to insert the information generated from select statement into the table but I am keep getting an errors, stating must declare as a table variable. If I declare as a table variable it stores the information into table variable and not insert the data into the table I want to take a look on below example
declare @tableName Varchar(100)
set @@tableName =’smtpF2.dbo.infoChange’;
Create table #temp
(
change_version_state varchar(max),
change_version_status varchar(200),
uniqueid varchar(20),
Country_code varchar(15),
Research_date varchar(15),
data_date varchar(14),
Make varchar(50),
Model varchar(50),
Versions varchar(255),
Model_year varchar(15),
doors varchar(10),
body varchar(10));
insert into #temp select * from infoChange
select * from #temp
declare @tableNames Varchar(100)
set @tableNames =’smtpF2.dbo.infoChange’;
insert into @tableNames select * from #temp
Your urgent reply / advise will be very appreciated
Thanks
Please ignore the first two lines as it was mistakenly copied (temp table declaration on top) example starts from create…
Thanks
Thanks Pinal.
Hello, Could anyone explain about merge in sql ? what work it does. Plz be openly
SIR WHERE ARE THE TABLE VARIABLE STORED…….IN THE MEMORY OR DISK…AND HOW?
Hello pinal, can you explain me, where table variable stored exactly, I have confused because some where i read that they are stored in tempdb and some where i read they stored in memory……I created table variable but i can’t find in tempdb. Please clear my confusion…………..