Table Type Variable:
DECLARE @Cars table
(
Car_id int NOT NULL,
ColorCode varchar(10),
ModelName varchar(20),
Code int,
DateEntered datetime
)
INSERT INTO @Cars (Car_id, ColorCode, ModelName, Code, DateEntered)
VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())
SELECT Car_id, ColorCode, ModelName, Code, DateEntered FROM @Cars
Temporary Table:
CREATE TABLE dbo.#Cars
(
Car_id int NOT NULL,
ColorCode varchar(10),
ModelName varchar(20),
Code int,
DateEntered datetime
)
INSERT INTO dbo.#Cars (Car_id, ColorCode, ModelName, Code, DateEntered)
VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())
SELECT Car_id, ColorCode, ModelName, Code, DateEntered FROM dbo.#Cars
DROP TABLE dbo.[#Cars]
•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.
•Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features. So for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated.
•Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.
•You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.
•You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).
For example:
BEGIN TRAN
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )
insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
insert into #temp
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
select * from #temp
select * from @var
ROLLBACK -- Temp table scope over here
select * from @var
select * from #temp
--OR--
BEGIN TRAN
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )
insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
insert into #temp
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
select * from #temp
select * from @var
GO -- Here Table type Variable scope will over
select * from #temp
select * from @var
There are many more differences between Table type variable and Temporary table
