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.
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.
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.
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.
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
Related Posts::
basic of view
insert into view with multiple tables
Insert into view with single table
Instead of update trigger and after update trigger in sql
Instead of Insert trigger and after insert trigger in sql
instead of delete trigger and after delete trigger in sql
basic of trigger and interview quetions on trigger
Knowledged Queries and Interview Queries
Comman Table Expression
Functions In Sql
No comments:
Post a Comment
if you have any doubt any suggestions do comment