Table Variable In SQL

                              Table Variable in SQL

Table variable are like TemporaryTable to store data temporarily but the difference is that Table Variable have a scope with in the Batch,function and in Stored Procedure only.
Many people have misconception that table variable are stored in Memory The storage is same as  likeTemporary Table, Table Variable are also stored in TempDB database.

Table Variable are like local variable hence Can be used inside Batch UDF Functions and Stored Procedures and they automatically cleaned up once procedure,batch or function executes.

Table Variable will give better performance over temporary table when dataset is small ie with in page Size 8 kb as like temporary table data table variable are also kept in memory chache.

But with larger Dataset it's good to choose Temporary table as temporary tables can have Indexes which a

Table Variable Can't.

--Syntax of Creating Table Variable

Declare @Employee as Table

( Empid nvarchar(30),empName nvarchar(30))

insert into @employee values ('1','xyz'),('2','abc')

select * from @employee 

Go

Now try to Run only this part in same session 

select * from @employee 

will throw an error Must declare the table variable

Now It's makes you clear about the scope of temporary table.

Note:There are Many other difference between Temp Table and Table Variable will explain all in my upcoming blogs.