Table Variables

What is Table Variables

Table variables like variable, its creating as variable like DECLARE statement.

Table variable table’s schema create in tempdb database and data store in Memory and its name start with hash (#) then add  random 8 digit like a Local temp table but it’s can’t explicitly accessible, it will automatically drop when batch completed its behavior like a In-Memory. Table variable is only accessible within the current batch or procedure.

Proof of Table Create in Tempdb:

• i have single user server and their temp table not exist

• I have declare table variable and inserting huge data(around 23000000 rows)  into table variable 

declare @tbl TABLE(ID BIGINT IDENTITY PRIMARY KEY,name varchar(100));
 insert into @tbl(name)
 select  a.name from sys.objects a,sys.objects b,sys.objects c,sys.objects d;

above query taking time around 5 mints

Now recheck tempdb’s temporary tables like: 

Yes their new local temp table exists and also its name start with # and its name length is 8 digit

Note: In Tempdb table variable’s schema and linking created but records store in memory so if we checking tempdb side its will not increase but memory consume.

If dataset is very small then performance comes good else temp table is useful when large amounts of data.

Comparison of Temp table vs table variable

SYNTAX
CREATE TABLE #tmptbl1(Id INT, Name VARCHAR(70)) DROP TABLE #tmptbl1	DECLARE @Customer TABLE(Id INT,Name VARCHAR(70)) variable table can’t Droppable
DDL Operation
Temp Table fully supported to DDL & DML like ALTER, CREATE, DROP etc,Table and index	Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP etc, Explicitly we can’t modify the structure of Table variable and its index nor we can drop. But implies allow like declare @tbl TABLE(ID int IDENTITY PRIMARY KEY,name varchar(100),ref_id int default 1); insert into @tbl (name) values (‘sdfsadfd’); select * from @tbl;
Storage
Temporary Table & Table Variable is that: Temporary Tables are created in TempDB	Table Variables are created In-Memory but also use TempDB
Transections
explicit transactions are allow   create TABLE #temptbl(ID int identity,name varchar(100));   begin TRAN   INSERT INTO #temptbl (name )VALUES(‘abc’);    ROLLBACK    SELECT * from #temptbl   Their 1 row inserted inside of transection and transection is rollback so record also delete from temp table.	explicit transactions not allow like   declare @tbl TABLE(ID identity,name varchar(100));   begin TRAN   INSERT INTO @tbl (name )VALUES(‘abc’);     ROLLBACK     SELECT * from @tbl   Their 1 row inserted inside of transection and transection is rollback but record not delete means records still exists into table variable
User Define Function
Temporary Tables are not allowed in User Defined Functions.	Table Variables can be used in User Defined Functions.
Index
During and After table creation Clustered & nonclustered index and all constraint are allow except foreign key	During table creation Clustered & nonclustered index and all constraint are allow except foreign key  but after table creation we can’t change anything   declare @tbl as table (id int,name varchar not null unique, primary key NONCLUSTERED(id))
Scope
Local temp tables are only accessible in same session till table not explicitly drop or connection not close OR Procedure till complete. Global temp Table dropped explicitly or they will get dropped automatically when the session which created it close.   Its need more IO resources and generally use for large data set	Scope of the Table variable is the Batch or Stored Procedure in which it is declared.   Its need less IO resources because its create in memory and generally use for small data set   begin             declare @tbl TABLE(ID BIGINT IDENTITY PRIMARY KEY,name varchar(100) );             insert into @tbl (name) values (‘sdfsadfd’); end  select * from @tbl; ; — access GO select * from @tbl; — can’t access
Scope In Side Child procure
Parent temp table will be access in Child procedure Like    CREATE proc sp_t2 as begin             select *from #temp; end CREATE proc sp_t1 as begin             CREATE TABLE #temp(ID BIGINT IDENTITY PRIMARY KEY,name varchar(100));             insert into #temp (name) values (‘abc’);             exec sp_t2; end	Table Variable table can’t access in side of Child procedure (procedure can’t create its give syntax error when creating proc)