What is Temporary Table And Variable Table ? And What is the use of them or Purpose of them?

Here is the post explaining the use and difference between Temporary Table and Temporary Variable.

http://www.dotnetfunda.com/interviews/show/297/what-is-temp-table-and-table-variable-in-sql-server

Thanks

Best regards,
Webmaster
http://www.dotnetfunda.com

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

@tableName Table variables are alive for duration of the script running only i.e. they are only session level objects.

Example:

DECLARE @tab TABLE ( ID int, name varchar(100))

To test this, open two query editor windows under sql server management studio, and create table variables with same name but different structures. You will get an idea. The @tableName object is thus temporary and used for our internal processing of data, and it doesn't contribute to the actual database structure.

There is another type of table object which can be created for temporary use. They are #tableName objects declared like similar create statement for physical tables:

Create table #test (Id int, Name varchar(50))
This table object is created and stored in temp database. Unlike the first one, this object is more useful, can store large data and takes part in transactions etc. These tables are alive till the connection is open. You have to drop the created object by following script before re-creating it.

IF OBJECT_ID('tempdb..#test') IS NOT NULL

  DROP TABLE #test 

Refer below links for more differences between temp table and table variable
http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/
http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article
http://www.codeproject.com/Articles/18972/Temporary-Tables-vs-Table-Variables-and-Their-Effe

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.

Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.

Thanks & Regards,
Mani Kumar

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Mark as Answer if got clear idea/solution...

Post us back the issue/doubts if not resolved the issue

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

When you handle the data in backend at that time most of the developers go with creating temporary tables and save those information into temporary tables and manipulate it based on that data. But No one is looking for the performance issues.

Now, we are discussing about that then you can understand what is Temporary table and what is Table variable.

EX:

Select col1,col2
into #temp

from tablename

In above query I just store the information into one temporary table name it as "#temp", the problem with this is we need to destroy it by manually to drop that temporary table, this means temporary table occupy some memory. But using Table Variable it won't occupy the memory it's automatically destroy the object.

Ex:

declare @table table
(
   col1 datatype,
   col2 datatype
)

insert into @table
select col1,col2 from tablename

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator