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   interview questions urls   External Links  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 ?    0 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 ?    0 Yes 0 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 ?    0 Yes 0 No
Jerry Joseph
 

 
 
 
Other SQL Server Interview Questions
 
  Question Asked @ Answers
 
how many types of store procedre in sqlserver 2000? ATI2
write the query for taking database restore in sql?  1
how to find nth highest salary  21
How do we get current date in SQL Server 2000, Oracle, MS Access?  10
Advantages and Disadvantages of Cursor? Zenith4
What are constraints? Explain different types of constraints?  4
how to give input dynamically to a insert statement in sqlserver HCL1
Name three version of sql server 2000 and also their differences?  1
What are Sql Reporting Services and analysis services? Can u Explain  1
What are user defined datatypes and when you should go for them?  1
write down the sql query? Table Name : emp1 empid empname 1 bala 2 guna Table Name : emp2 empid empname 1 <Null> 2 <Null> Solution : emp1 names are updated in emp2, write a query?  3
What is the difference between views and stored procedures? Can we have input parameters for views?  3
what is IDE,DMV in sql server? Value-Labs1
What is a Linked Server?  1
As a part of your job, what are the DBCC commands that you commonly use for database maintenance? HCL1
how insert selected column only ? Robert-Half1
What is the system function to get the current user's user id? HCL1
What is transaction ? Give me one example. Melstar7
i want to join two queries....and i want to run them as one after another that is want output of first query then second , then again output of first query then second and so on...  1
1.what is stored procedure?Its significance with example? 2.Explain about index with syntax and example? plz do reply.........  1
 
For more SQL Server Interview Questions Click Here 
 
 
 
 
 
   
Copyright Policy  |  Terms of Service  |  Help  |  Site Map 1  |  Articles  |  Site Map  |   Site Map  |  Contact Us
   
Copyright © 2007  ALLInterview.com.  All Rights Reserved.

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