Temp Table Vs Table Variable

Temp tables behave same as normal tables and are bound by transactions. Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction. 


Transaction logs are not recorded for the table variables, hence they are out of scope of the transaction mechanism. While Transaction logs are recorded for the temporary table.


Any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. 


Table variables don't participate in transactions, logging or locking. This means they're faster.


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


You can create a temp table using SELECT INTO, which can be quicker to write and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.


Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.

Temp Table VS Table Variable in SQL Server

Here are some differences between Temp Table and Table Variable in SQL Server

Temp Table	Table Variable
Temp table is valid for a session. For eg: when you run the following code create table #temp(i int) insert into #temp select 345 Go create table #temp(i int) insert into #temp select 345 Go you will get an error	Table variable has a statement-level scope. ie as soon as you execute the statement the scope is lost For eg: when you run the following code declare @t table(i int) insert into @t select 45 GO declare @t table(i int) insert into @t select 45 GO you will not get an error
It is possible to alter the temp table to add columns, idexes,etc	It is not possible to alter a table variable
It is possible to truncate a temp table	It is not possible to truncate a table variable
SELECT INTO method can be used for temp table SELECT * INTO #temp from your_table	SELECT INTO method cannot be used for table variable. You get error for the following SELECT * INTO @t from your_table
Temp table can be useful when you have a large amount of data	For small set of data, table variables can be useful