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,First of all i would say Good Morning it is 4:41am here in u.s.a,
my question is if tablevariable does not rollback then is it saved at dick like regular tables or it flush out once your session ends or it flush out upon your restart of sql server?
I think so upon ending of sessions but would like to confirm.
Regard
Iyasir
Hi Pinal,First of all i would say Good Morning it is 4:41am here in u.s.a,
my question is if tablevariable does not rollback then is it saved at disk(in database–datafile) like regular tables or it flush out once your session ends or it flush out upon your restart of sql server?
I think so upon ending of sessions but would like to confirm.
Regard
Iyasir
And what is the difference between local variable and table variable please?
Local variable can be of any type and can have only one value. Table varialbe is of type table and it just act like a table where you can have multiple columns with multiple rows
Hi,
All declared variables in SQL server are local variables and are available only in current session. There is nothing to specify the scope of variable. Table vriable is a variable of data type “Table”. Here Table does not specify the scope of variable but specify the data type of variable.
Regards,
Pinal Dave
hi Pinal Dave
i do have a temporary table definition in a variable.
how can i created this table ?
can i use exec @var?(var contains definition of temp table )
I need to do so because no of columns depends on user input
Please suggest me
Hello Feroz,
You can create temp table using EXEC statement but that would be available only in EXEC statement. For example:
EXECUTE ( ‘CREATE TABLE #SalesTbl
(SalesID int, SalesName varchar(10)) ;
INSERT INTO #SalesTbl VALUES(1, ”ss”)
SELECT * FROM #SalesTbl’) — success
SELECT * FROM #SalesTbl — Error
Because EXEC starts a new session and temp table created inside that session is not accessible outside the EXEC () script. If you create a global table than that would be accessible as below:
EXECUTE ( ‘CREATE TABLE ##SalesTbl
(SalesID int, SalesName varchar(10)) ;
INSERT INTO ##SalesTbl VALUES(1, ”ss”)
SELECT * FROM ##SalesTbl’ ) — success
SELECT * FROM ##SalesTbl — success
Regards,
Pinal Dave
thanks Pinal Dave
hi Pinal Dave
is there any other alternative to my problem as i’m s/w developer and the global temporary table will be accessed by other user also.
i’m having definition in a variable like
DeclARE @sql varchar(100)
set @sql=‘CREATE TABLE #SalesTbl
(SalesID int, SalesName varchar(10)) ;)
exec(@sql)
now i want to insert records?
this temporary table is expired
if i will use global temporary then it will be shared by other users
so Please suggest me
Hi Feroz,
Append all insert statment to variable @sql and then execute. As below
@sql = @sql + ‘INSERT INTO #SalesTbl VALUES(,,,) ‘
@sql = @sql + ‘INSERT INTO #SalesTbl VALUES(,,,) ‘
exec (@sql)
Regards,
Pinal Dave
i have 2 temporary tables with these columns
file_no
amount
date
and another table
file_no
day1
day2
.
.
.
day31
what i have to do is select the day part of the date(suppose 12/18/2009 i have to select 18) and put the value of amount into another tables column of that day (suppose day18)
is there any procedure to overcome this problem
Will this help?
SELECT ‘day’ + CAST(DAY(Date) AS CHAR(2))
this table is created dynamically using
DeclARE @sql varchar(100)
set @sql=‘CREATE TABLE #SalesTbl
(file_no int,amount int,date datetime) ;)
exec(@sql)
file_no
amount
date
is the above procedure possible
I think Pinal already answered you. But here’s how you can do it. Take notice that you must set the all the statements into @sql variable and then execute is as whole. You can’t execute them separately because #SalesTbl is destroyed right after the exec command returns.
create procedure foo
as
begin
declare @sql varchar(100)
set @sql=’CREATE TABLE #SalesTbl(file_no int,amount int,date datetime); ‘
set @sql+=’SELECT * FROM #SalesTbl’
exec(@sql)
end
exec dbo.foo
drop procedure foo
hi Pinal Dave
as like you said that i did below that but i got the error in the @TableVar
–Create Table Variable and insert single row
DECLARE @TableVar TABLE(Col1 VARCHAR(100))
INSERT INTO @TableVar (Col1)
VALUES(‘Table Var – Outside Tran’);
SELECT Col1 AS TableVar_BeforeTransaction
FROM @TableVar
THis is the error
Msg 1087, Level 15, State 2, Line 2
Must declare the table variable “@TableVar”.
can you explain that why error will come
Thanks & Regards,
D.Ponraj
Hi Ponraj,
You need to make sure that you are executing all these queries at once.
This is table variable, so we need to declare at every execution time.
So here I think what did you do is:
First execute declare and insert statements and then you try to execute “select”. IN Table variable, you need to execute all statements at once then only you will get an actual result.
Scope of table variable is per execution.
Thanks,
Tejas
Thanks Tejas Shah…
i got the answer
I like the article. Most of the time I request developers to use table variables if the number of rows processing is small. However, I would like to know the benchmark of using table variable instead of temp table. Please share if any have have Idea about this.
Hi Pinal,
I want to create table by the using procedure and pass it table name as variable as like below.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ProcInsTab]
@Emp_ID varchar(50),
@Password varchar(max),
@Emp_Name varchar(100),
@Address varchar(250),
@City varchar(50),
@Mobile_No varchar(10)
AS
CREATE TABLE @Emp_ID(
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Catalog Version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mailed] [float] NULL ,
[Sales] [money] NULL ,
[Production $] [int] NOT NULL ,
[Orders] [float] NULL ,
[Response] [float] NULL ,
[Response of Test Control] [int] NOT NULL ,
[Average Invoice] [float] NULL ,
[SMP] [float] NULL ,
[SMP of Test Control] [int] NOT NULL ,
[Catalog Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Brand] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Drop Date] [smalldatetime] NULL
) ON [PRIMARY];
INSERT INTO [Employee_Master]
([Emp_ID]
,[Password]
,[Emp_Name]
,[Address]
,[City]
,[Mobile_No])
VALUES
(@Emp_ID, @Password,@Emp_Name, @Address,@City, @Mobile_No);
Please help me how to solved this problem.
Hello Ravi,
Prepare the CREATE TABLE statement dynamically to create global or permanent table because temporary or table variable would not be accessible outside of execution statement.
Regards,
Pinal Dave
Hi Pinal Dave,
I have modified the above query by adding one more variable to store the SQL Statement in SQLString,
When executed, I got the error in the @TableVar
— Start —
DECLARE @TableVar TABLE(Col1 VARCHAR(100))
DECLARE @SqlString as varchar(max)
SET @SqlString = ‘INSERT INTO @TableVar (Col1) VALUES(”Table Var – Outside Tran”)’
EXEC (@SqlString)
SET @SqlString = ‘SELECT Col1 AS TableVar_BeforeTransaction FROM @TableVar’
EXEC (@SqlString)
— End —
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable “@TableVar”.
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable “@TableVar”.
——————————————————–
Please help me how to solved this problem.
Hi Sanjay,
Everytime we execute a batch in EXEC function a new session is created and tem tables and table variables created in earlier session becomes unaccessible. If you create a single batch then you can get the desired result as below:
DECLARE @SqlString as varchar(max)
SET @SqlString = ‘DECLARE @TableVar TABLE(Col1 VARCHAR(100))’
SET @SqlString = @SqlString + ‘INSERT INTO @TableVar (Col1) VALUES(”Table Var – Outside Tran”)’
EXEC (@SqlString)
SET @SqlString = @SqlString + ‘SELECT Col1 AS TableVar_BeforeTransaction FROM @TableVar’
EXEC (@SqlString)
Regards,
Pinal Dave
Hi Pinal,
I want to create table permanent by the using procedure and pass it table name as variable as like below.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ProcInsTab]
@Emp_ID varchar(50),
@Password varchar(max),
@Emp_Name varchar(100),
@Address varchar(250),
@City varchar(50),
@Mobile_No varchar(10)
AS
CREATE TABLE @Emp_ID(
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Catalog Version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mailed] [float] NULL ,
[Sales] [money] NULL ,
[Production $] [int] NOT NULL ,
[Orders] [float] NULL ,
[Response] [float] NULL ,
[Response of Test Control] [int] NOT NULL ,
[Average Invoice] [float] NULL ,
[SMP] [float] NULL ,
[SMP of Test Control] [int] NOT NULL ,
[Catalog Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Brand] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Drop Date] [smalldatetime] NULL
) ON [PRIMARY];
INSERT INTO [Employee_Master]
([Emp_ID]
,[Password]
,[Emp_Name]
,[Address]
,[City]
,[Mobile_No])
VALUES
(@Emp_ID, @Password,@Emp_Name, @Address,@City, @Mobile_No);
Please help me how to solved this problem.
Hi pinal,
My question is that – Is Table variable hits the performance? if yes then how and how we can manage it..