In many scenarios we need some temporary table for the processing of data. Now you have two option 1) Table Variable, 2) temp Table. Which one do you choose? Let's talk about differences between these two and make sure our decision to use one of them is best for our requirement.
|Table Variable||Temp Table|
|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.||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 than temporary tables are a good option|
|You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (e.g. make a function to split a string into a table of values on some arbitrary delimiter).||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 data types over time, since you don't need to define your temp table structure upfront.|
|A table variable can only have a primary index,||A temp table can have indexes|
|If speed is an issue Table variables can be faster||But if there are a lot of records, or the need to search the temp table of a clustered index, then a Temp Table would be better.|
|A table variables don't have column statistics, This means that the query optimizer doesn't know how many rows are in the table variable (it guesses 1), which can lead to highly non-optimal plans been generated if the table variable actually has a large number of rows||Whereas temp tables do have column statistics so the query optimizer can choose different plans for data involving temp tables|
|You cannot alter a Table variable with DDL statement (so you cannot create a non clustered index on a table variable). That makes every table variable a heap, or at best a table with a single, clustered index, and every table variable access a table scan (or clustered index scan)||Temp tables can be altered with DDL statements|
|User table variable if there less records for processing||Use temp table if you have huge record for processing|
|-To create table variable|
declare @T table
insert into @T select 'some value'
select * from @T
|-You can create temp table|
create table #T
insert into #T select 'some value'
select * from #T
So now I think you can make wise decision which one to use when. Have fun -J