Difference between table variable and temp table in SQL Server - WebNetGuru.com

Difference between table variable and temp table in SQL Server


This is confusing question to the SQL developer that what is a exact difference between SQL server temporary table and table variable ?

Let's see the difference on structural level and usage level.



Properties Temporary Table (#table) Table Variable (@table)
Declaration Use # Key to crate table Use @ Key to crate table
Behavior It behave as table It behave as variable
Scope Limited to current connection Limited to current connection
Location Create in tempdb database Create in memory
Indexes Allow to create indexes

what is diffrence between temporary table and global temporary table
Not allow to create indexes

what is diffrence between temporary table and global temporary table
Fast Less faster than table variable Faster than temporary table
Pass Temporary Table can't pass as parameter to function and procedure Table variable can pass as parameter to function and procedure
Drop Can drop because physically available

what is diffrence between #table and ##table
Can't drop because not physically available
what is diffrence between #table and ##table
Example
CREATE TABLE #tempTable
(
Id INT ,
StudentName VARCHAR (100) ,
Std TINYINT ,
ContactNumber VARCHAR (10)
)
DECLARE @tempTable TABLE
(
Id INT ,
StudentName VARCHAR (100) ,
Std TINYINT ,
ContactNumber VARCHAR (10)
)


Move Top