What is the difference between temp table and table variable?
- 8 Answers
- 25594 Views
- Microsoft, I also Faced
- E-Mail Answers
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.
| Is This Answer Correct ? | 13 Yes | 0 No |
Post New Answer View All Answers
what is database replicaion? : Sql server database administration
let's assume you have data that resides on sql server 6.5. You have to move it sql server 7.0. How are you going to do it? : Sql server database administration
What triggers long term care?
What is the process of normalising?
What is meant by indexing?
How many partitions a clustered index has by default in sql server 2012?
Write a sql query to delete duplicate records from a table called table1
What are “unrepeatable reads”?
Are resultset updatable?
How to run queries with sql server management studio express?
What happens if you are trying to access a schema not owned by you?
What are the different acid properties?
What do you understand by coalesce in sql server?
How can you ensure that the database and sql server based application perform well?
What is a document index?
