Differencebetween Temporary Table and Table Variable in SQL 2008

What's the difference between a temp table and table variable in SQL Server?

There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn't one of them, as spelt out in the MSDN link below.

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)

Some points to consider when choosing between them:

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 can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.
Table variables don't participate in transactions and SELECTs are implicitly with NOLOCK. The transaction behaviour can be very helpful, 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).
Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.
Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb (ref). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.
Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

Some further reading:

Martin Smith's great answer on dba.stackexchange.com
MSDN FAQ on difference between the two: https://support.microsoft.com/en-gb/kb/305977
MDSN blog article: https://learn.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table
Article: https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables
Unexpected behaviors and performance implications of temp tables and temp variables: Paul White on SQLblog.com

Table variable vs temp table in SQL Server 2008

The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:

create table #T (s varchar(128)) 
declare @T table (s varchar(128)) 
insert into #T select 'old value #' 
insert into @T select 'old value @' 
begin transaction 
     update #T set s='new value #' 
     update @T set s='new value @' 
rollback transaction 
select * from #T 
select * from @T

Read more : http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

What is the difference between TEMPORARY TABLE and TABLE VARIABLE in SQL 2008?

Temporary tables are like ordinary tables in most characteristics, except they go into TempDB instead of the current Database, and they dissapear after limited scope, (depending on whether they are session based or global Temp Tables. But all changes to data in Temp tables is logged to the transaction log, with all the performance implications that that entails. otoh, you can also add as many indices or views, or triggers, or whatever else you want to a temp table exactly as you would to a ordinary table.

Table variables are a kind of short-cut in-memory table (they also use temp DB). Changes to them are not logged (this improves performance). But you can only get one index on them, (because indices cannot be created after the initial declaration statement, the only index you can create on a table variable is the one that can be included in the initial table variable declaration...

   Declare @Tab Table (myKey integer Primary Key Not Null, data varchar(20))

Because of these characteristics, temp tables are better choice for large tables, (wide and with many rows), and/or that will undergo more than one access pattern during their lifetime, whilst table variables are best when you need a very narrow table (keys only table, or key with only one data column), which will always be accessed by that indexed key...

Is using Table variables faster than temp tables

Temp tables are better in performance. If you use a Table Variable and the Data in the Variable gets too big, the SQL Server converts the Variable automatically into a temp table.

It depends, like almost every Database related question, on what you try to do. So it is hard to answer without more information.

So my answer is, try it and have a look at the execution plan. Use the fastest way with the lowest costs.

MSDN - Displaying Graphical Execution Plans (SQL Server Management Studio)

Difference between temp table, table variable, global temp table to use in stored procedure in SQL Server 2008

The rule is pretty much, choose a table variable as the default. Then re-assess the situation in the following circumstances:

If you want to use transaction rollback
If you want to pass the resultsets of your tables from one stored procedure to another
If you want the query optimizer to be able to work out how best to run a complex query
If you’re getting really clever with dynamic SQL, you need a temporary table.

So, table variables ought to be your first choice. For small result sets and the everyday type of data manipulation, they are faster and more flexible than temporary tables, and the server cleans them up when you’ve finished.
Temp variable does not persist in tempdb unlike temp table, it will be cleared automatically immediately after SP or function. Temp variable can only have 1 index i.e primary, TT can have more indexes. Use temp variables for small volume of data and vice versa for TT. No data logging and data rollback in variable but for TT it’s available. Less SP recompilations with variables than TT.

Global temp tables are just like temp tables except that they are visible to all sessions as long creating session is valid. Hope this gives you good idea which way to go given your scenario.

SQL Server Temp table vs Table Variable

Table variables can lead to fewer stored procedure recompilations than temporary tables (see KB #243586 and KB #305977), and — since they cannot be rolled back — do not bother with the transaction log.

##table is belogs to global temporary table. yes #table not exist because its in given scope only and you never access it out the given scope.

Edit

I also like to point make use of CTE(Common Table Expressions) because it also somehow work as temporary table.
Check this answer for detail : Which are more performant, CTE or temporary tables?

When should I use a table variable vs temporary table in sql server?

Your question shows you have succumbed to some of the common misconceptions surrounding table variables and temporary tables.

I have written quite an extensive answer on the DBA site looking at the differences between the two object types. This also addresses your question about disk vs memory (I didn't see any significant difference in behaviour between the two).

Regarding the question in the title though as to when to use a table variable vs a local temporary table you don't always have a choice. In functions, for example, it is only possible to use a table variable and if you need to write to the table in a child scope then only a #temp table will do
(table-valued parameters allow readonly access).

Where you do have a choice some suggestions are below (though the most reliable method is to simply test both with your specific workload).

If you need an index that cannot be created on a table variable then you will of course need a #temporary table. The details of this are version dependant however. For SQL Server 2012 and below the only indexes that could be created on table variables were those implicitly created through a UNIQUE or PRIMARY KEY constraint. SQL Server 2014 introduced inline index syntax for a subset of the options available in CREATE INDEX. This has been extended since to allow filtered index conditions. Indexes with INCLUDE-d columns or columnstore indexes are still not possible to create on table variables however.
If you will be repeatedly adding and deleting large numbers of rows from the table then use a #temporary table. That supports TRUNCATE (which is more efficient than DELETE for large tables) and additionally subsequent inserts following a TRUNCATE can have better performance than those following a DELETE as illustrated here.
If you will be deleting or updating a large number of rows then the temp table may well perform much better than a table variable - if it is able to use rowset sharing (see "Effects of rowset sharing" below for an example).
If the optimal plan using the table will vary dependent on data then use a #temporary table. That supports creation of statistics which allows the plan to be dynamically recompiled according to the data (though for cached temporary tables in stored procedures the recompilation behaviour needs to be understood separately).
If the optimal plan for the query using the table is unlikely to ever change then you may consider a table variable to skip the overhead of statistics creation and recompiles (would possibly require hints to fix the plan you want).
If the source for the data inserted to the table is from a potentially expensive SELECT statement then consider that using a table variable will block the possibility of this using a parallel plan.
If you need the data in the table to survive a rollback of an outer user transaction then use a table variable. A possible use case for this might be logging the progress of different steps in a long SQL batch.
When using a #temp table within a user transaction locks can be held longer than for table variables (potentially until the end of transaction vs end of statement dependent on the type of lock and isolation level) and also it can prevent truncation of the tempdb transaction log until the user transaction ends. So this might favour the use of table variables.
Within stored routines, both table variables and temporary tables can be cached. The metadata maintenance for cached table variables is less than that for #temporary tables. Bob Ward points out in his tempdb presentation that this can cause additional contention on system tables under conditions of high concurrency. Additionally, when dealing with small quantities of data this can make a measurable difference to performance.

Effects of rowset sharing

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T

SQL Server - Table variable vs temporary table with a select statement with union

Another option would be using a CTE, and the way you're describing this, this is pretty much what CTEs are for:

;WITH cte as
(
    SELECT C1,C2, ... FROM Source1
    UNION ALL
    SELECT C1,C2,... FROM Source2
    UNION ALL
    SELECT C1,C2 from Source3
) 
SELECT cte.*,
       (CASE WHEN ...
        ELSE ''
        END) as CN
FROM cte;

But a few things to consider around CTE vs table var vs temp table:

(tl;dr: CTEs are reusable within a single query, table variables and temp tables are reusable within many queries and have some different memory/indexing capabilities.)

CTEs are reusable within your query and can end up being a little cleaner than subqueries, but some people are just used to subqueries. In some cases, I've seen the optimizer do a better job on CTEs than with subqueries, but it usually pans out.
Table variables may be stored in memory or on disk; CTEs basically work this way too, but the big difference is that a table variable can have a primary key and unique keys added to it. You're not using that here, so no big deal. Another big difference is that table variables can be declared as reusable types - if this is a format that you use in a lot of places and want to maintain the structure of it, that's helpful. It's also helpful if you're using SQL 2014 or 2016, where table variables can be declared as OLTP in Memory Tables. Temp tables can't do that, and neither can CTEs - this is basically a very optimized version of the table for high contention scenarios that are better handled in memory with latchless structures rather than the normal SQL latching process. Table variables can also be passed as parameters to other stored procedures.
Temp tables can have any number of indexes added to them. They also can be declared as global temp tables. They're going to reside on disk, and they'll add work to your tempdb. They can't be declared as in memory OLTP tables. They can't be passed as parameters, but they can be shared between procedures (whether as global temp tables or as a temp table accessible within the same batch)
Both temp and table variables can have other DML statements run on them after the initial INSERT - if you need to update/delete/insert more data in them that can work. CTEs can't do that (if you run DML on a CTE, it will insert/update/delete the underlying table(s)).

SQL Server tables: what is the difference between @, # and ##?

#table refers to a local (visible to only the user who created it) temporary table.

##table refers to a global (visible to all users) temporary table.

@variableName refers to a variable which can hold values depending on its type.