Sql Server Temporary Tables vs Table Variables
Temp Table Syntax:
create
table #Emp (...)
Table - Variable Syntax:
declare
@Emp table (...)
Scenario: Temp Table and Table Variable are assigned
with Value and Updated with New Value. After that we rolled back both. Which
one will give the Previous Value
1)The first one
is that transaction logs are not recorded for the table-variables. Hence, they
are out of scope of the transaction mechanism, as is clearly visible
--Table Variable
declare @Emp1 table (empid int,ename varchar(10))
insert into @Emp1 values(1,'Ravi')
begin tran
update @Emp1 set empid=3
rollback tran
select * from @Emp1
--Temp Table
drop table #Emp
create table #Emp(empid int,ename varchar(10))
insert into #Emp values(1,'Ravi')
begin tran
update #Emp set empid=3
rollback tran
select * from #Emp
------------------------Another
Example
drop table #T
create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction
update #T set s='new value #'
update @T set s='new value @'
rollback transaction
select * from #T
select * from @T
s
---------------
old value #
s
---------------
new value @
2)The second
major difference is that any procedure with a temporary table cannot be
pre-compiled, while an execution plan of procedures with table-variables can be
statically compiled in advance. Pre-compiling a script gives a major advantage
to its speed of execution. This advantage can be dramatic for long procedures,
where recompilation can be too pricy.
3)Table-variables
exist only in the same scope as variables. Contrary to the temporary tables,
they are not visible in inner stored procedures and in exec(string) statements.
Also, they cannot be used in insert/exec statement.
4) Table Variables and Temp Tables both use the tempdb
----table variable
4) Table Variables and Temp Tables both use the tempdb
----table variable
DECLARE @TableVariable TABLE (DT DateTime DEFAULT GETDATE() NOT NULL)
INSERT INTO @TableVariable DEFAULT VALUES
--2
WAITFOR DELAY '00:00:02'
----Temp Table
CREATE TABLE #TempTable (DT DateTime DEFAULT GETDATE() NOT NULL)
INSERT INTO #TempTable DEFAULT VALUES
--3
SELECT DT AS TableVariableTime FROM @TableVariable
SELECT DT AS TempTableTime FROM #TempTable
--4
SELECT * FROM tempdb.sys.objects
WHERE type = 'U'
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment