Question:
Apparently there are two ways to create a temporary table in SQL-Server:
With a table variable ( table variable )
DECLARE @tmp TABLE (Col1 INT, Col2 INT)
or with a temporary table
CREATE TABLE #tmp (Col1 INT, Col2 INT)
What is the difference between them?
Original question: What's the difference between a temp table and table variable in SQL Server?
Answer:
There are some differences between temporary tables (#tmp) and variables (@tmp), although using tempdb is not one of them, as discussed in the MSDN link below.
As a general rule of thumb, for small and medium volumes of data and simple usage scenarios you should use table variables. (This is of course too broad a guideline of exceptions – see below and after articles.)
Some points to consider when choosing between them:
Temporary tables are actually tables so you can do things like CREATE INDEXes, etc. If you are handling large amounts of data for which index access will be faster than temporary tables, these are a good option.
Table variables can be indexed 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 contains non-unique indexes too.
Table variables do not participate in transactions, logging, or locking. This means that they are faster, since they do not require the overload, but on the contrary they do not give you those characteristics. So for example, if you want to rollback (ROLLBACK) halfway through a procedure, then the table variables populated during that transaction will still be populated!
Temporary tables could lead to recompiled Stored Procedures. Table variables do not.
You can create a temporary table using SELECT INTO, which can be faster to write (good for an ad-hoc query) and can allow you to deal with changing data types over time, since you don't need to define the structure from your temporary table in advance.
You can pass table variables back from functions, which makes encapsulating and reusing logic much easier (for example, making a function to split a string into a table of values with some arbitrary delimiter).
Using table variables within user-defined functions allows those functions to be used more widely (see CREATE FUNCTION documentation for details). If you write a function you must use table variables on temporary tables unless there is an absolute necessity.
Both table variables and temporary tables are stored in tempdb. But table variables (since 2005) defaulted to current database collation compared to temporary tables which have default collation of tempdb ( reference ). This means that "Collation" details should be avoided if temporary tables are used and your "collation" is different from that of the tempdb, causing problems if you want to compare the data in the temporary table with the data in the database.
Global temporary tables (## tmp) are another type of temporary table available to all sessions and users.
Some further reading:
Great answer from Martin Smith at dba.stackexchange.com
MSDN FAQ about the difference between the two: http://support.microsoft.com/default.aspx/kb/305977
MDSN blog post: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
Article: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1267047,00.html#