Monday 17 June 2013

temp table and temp variable in sql server

                                        In this article we will see how to create temp table and table variable.
what is difference in both.As a rule of thumb, for small to medium volumes of data and
simple usage scenarios you should use table variables.
1)              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. with temp variablewe have not possible to use or
create indexes.

2)   Table variables dont participate in transactions, logging or locking. Temporary Tables
participate in transactions,logging or locking.
3)   You can create a temp table using SELECT INTO you can not create table variable
using select into.
4)   Both table variables and temp tables are stored in tempdb.
5)   Global Temp Tables (##tmp) are another type of temp table available to all sessions and
users. a table variable is available in currect session only.

6)Syntax of table variable::


declare @table_name as table (column_name datatype)

7)Syntax of Temporary Table::


a)local temp table ::

create table #table_name(column_name datatype)

b)global temp table ::

create table ##table_name(column_name datatype)

8)examples::


A)Table variable ::


declare @temp_table as table (id int ,s_name varchar(50))
insert into @temp_table (id,s_name) values(1,'sandipG')
select * from @temp_table

B)local temp table ::


create table #temp(temp_id int,temp_name varchar(50))
insert into #temp (id,s_name) values(1,'sandipG')
select * from #temp

C)global temp table ::


create table ##temp(temp_id int,temp_name varchar(50))
insert into ##temp (id,s_name) values(1,'sandipG')
select * from ##temp


No comments:

Post a Comment

if you have any doubt any suggestions do comment