what are the difference between temp table and table variable in sql server?
- Declaration
- Temp table is create by # sign with create keyword
- example: create table #TMPTABLE(id int, FName varchar(50),LName varchar(50))
- Table variable is declare by @ sign with
- example: declare @TableVar table (id int,name varchar(50))
- Scope of use
- Temp table can be use by multiple user
- Table variable can use by current user only
- Use of DDL command
- Temp table allow use of all DDL commands on it.
- Table variable do not allow DDL command on it
- Index
- Temp table allow to create alter and drop table index
- Table variable do not allow to create index but it only allow to create cluster index only.
- Transaction
- Temp table allows to begin rollback and commit transaction
- Any Transaction command is not possible with table variable.
- Storage
- Temp table is physically created in tempdb
- Table variable is create in memory
Example Temp table
create table #TMPTABLE(id int, FName varchar(50),LName varchar(50))
insert into #TMPTABLE values (1,'jayendra','jayendra')
select * from #TMPTABLE
Example Table variable
declare @TableVar table (id int,fname varchar(50),lname varchar(50))
insert into @TableVar values (2,'jayendra','jayendra')
select * from @TableVar
