ALLInterview.com :: Home Page KalAajKal.com
 Advertise your Business Here     
Browse  |   Placement Papers  |   Company  |   Code Snippets  |   Certifications  |   Visa Questions
Post Question  |   Post Answer  |   My Panel  |   Search  |   Articles  |   Topics  |   ERRORS new
   Refer this Site  Refer This Site to Your Friends  Site Map  Bookmark this Site  Set it as your HomePage  Contact Us     Login  |  Sign Up                      
info       Did you received any Funny E-Mails from your Friends and like to share with rest of our friends? Yeah!! you can post that stuff   HERE
Google
 
Categories  >>  Software  >>  Databases  >>  SQL Server
 
 


 

 
 Oracle interview questions  Oracle Interview Questions
 SQL Server interview questions  SQL Server Interview Questions
 MS Access interview questions  MS Access Interview Questions
 MySQL interview questions  MySQL Interview Questions
 Postgre interview questions  Postgre Interview Questions
 Sybase interview questions  Sybase Interview Questions
 DB Architecture interview questions  DB Architecture Interview Questions
 DB Administration interview questions  DB Administration Interview Questions
 DB Development interview questions  DB Development Interview Questions
 SQL PLSQL interview questions  SQL PLSQL Interview Questions
 Databases AllOther interview questions  Databases AllOther Interview Questions
Question
What is the difference between temp table and table variable?
 Question Submitted By :: Laxman
I also faced this Question!!     Rank Answer Posted By  
 
  Re: What is the difference between temp table and table variable?
Answer
# 1
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 ?    5 Yes 0 No
Balasubramaiam S( Ramco System
 
  Re: What is the difference between temp table and table variable?
Answer
# 2
1. If we use Temporary Table in a stored procedure, We   
should drop it at the end.
It is not necessary in the case of Table variable.

2. In Table variable we can hold maximum of 20000 records 
only. If it exceeds, we can use temporary table
 
Is This Answer Correct ?    5 Yes 5 No
Rathikapoobalan
 
 
 
  Re: What is the difference between temp table and table variable?
Answer
# 3
SYNTAX
Temporary Tables: CREATE table #T (…)
Table Variables: DECLARE @T table (…)

Table Variables are out of scope of the transaction mechanism.
If you make changes to a Temp Table inside a Transaction and
Rollback the Transaction those changes will be lost.
Changes made to Table Variable inside a Transaction will
remain even if you Rollback the Transaction

Any procedure with a temporary table cannot be pre-compiled
An execution plan of procedures with table variables can be
statically compiled in advance

Table Variables exist only in the same scope as variables.
They are not visible in inner stored procedures and in
exec(string) statements

Table variables are in-memory structures that may work from
2-100 times faster than temp tables. 

Access to table variables gets slower as the volume of data
they contain grows. 

At some point, table variables will overflow the available
memory and that kills the performance. 

Use table variables only when their data content is
guaranteed not to grow unpredictably; the breaking size is
around several thousand records. 

For larger data volumes, use temp tables with clustered
indexes.
 
Is This Answer Correct ?    5 Yes 1 No
Jerry Joseph
 
  Re: What is the difference between temp table and table variable?
Answer
# 4
Temp table create automatically when you create any data 
table create you must mention there create temporary table.
Table variable is must declare in procedure when you crate 
procedure.
 
Is This Answer Correct ?    0 Yes 2 No
Prakash
 

 
 
 
Other SQL Server Interview Questions
 
  Question Asked @ Answers
 
What is Schema? and why we use schemas?  1
please can anyone answer this query Table 1 has 2 columns: EmployeeId,Age Table 2 has 2 columns: EmployeeId, Region Write SQL to Find the region who has the oldest person  7
I am Having tables T1 and T2 both having same data how to check (or) compare the data in both table are same?  2
How many max. conditions can b written under the WHERE clause? Like select * from [tabnam] WHERE (cond1...or..cond2....or...cond3...and.....so on.....??? (upto how much extent))????? SAP-Labs2
What is The Use Of TIMESTAMP DataType in SQL Server 2005?  3
how to rest identity columns in sql server Matrix3
As a part of your job, what are the DBCC commands that you commonly use for database maintenance? HCL1
What is an extended stored procedure? Can you instantiate a COM object by using T-SQL? HCL3
How can we write or define DDL statements in Sql server and DML statements?  2
Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table? eFunds5
Can you tell me the difference between DELETE & TRUNCATE commands?  11
What is the use of placing primary key and foreign key constrains on columns. Value-Labs2
Explain sp_configure commands, set commands?  3
how to select 5 to 7 rows from a table, which contains 10 rows? IBM14
i want table name basis on column name.  5
How to count the number of duplicate items in a table?  1
How would you Update the rows which are divisible by 10, given a set of numbers in column?  1
What is the system function to get the current user's user id? HCL1
How do you check the performance of a query and how do you optimize it?  1
What is normalization?  6
 
For more SQL Server Interview Questions Click Here 
 
 
 
 
 
   
Copyright Policy  |  Terms of Service  |  Help  |  Site Map 1  |  Articles  |  Site Map  |   Site Map  |  Contact Us interview questions urls   External Links 
   
Copyright © 2007  ALLInterview.com.  All Rights Reserved.

ALLInterview.com   ::  Forum9.com   ::  KalAajKal.com