Answer Posted / balasubramaiam s( ramco system

Table variables are Transaction neutral. They are variables
and thus aren't bound to a transaction.

Temp tables behave same as normal tables and are bound by
transactions.

BEGIN TRAN
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )

insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'

insert into #temp
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'

select * from #temp
select * from @var

ROLLBACK

select * from @var
if object_id('tempdb..#temp') is null
select '#temp does not exist outside the transaction'


We see that the table variable still exists and has all
it's data unlike the temporary table that doesn't exists
when the transaction rollbacked.

How to start and end transact-sql statements?

1002

---

What are the different sql server versions?

962

---

how to take backup bcp out for a column in table in sql server?

2081

---

How to read data in a table with "select" statements?

950

---

Explain the steps needed to create a scheduled job?

911

---

What is an index. What are the types?

1059

---

How to sort query output in descending order in ms sql server?

927

---

What are logical/boolean operations in ms sql server?

1015

---

what is create database syntax? : Sql server database administration

1083

---

What are the triggers in sql?

1071

---

What stored by the model?

1032

---

How to connect php with different port numbers?

1059

---

What are drillthrough reports?

155

---

When does a workload on SQL Azure get throttled?

129

---

Explain the rules for designing files and file groups in sql server?

965

---