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 ( temporary table )
CREATE TABLE #tmp (Col1 INT, Col2 INT)
What is the difference between them?
Original question: What is the difference between a variable table and a table in SQL Server?
There are some differences between the temporary tables (#tmp) and variables (@tmp), although the use of tempdb is not one of them, as explained in the below link MSDN.
As a general rule, for small and medium data volumes and simple usage scenarios you should use the table variables. (This is too broad a pattern of course many 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 handle large quantities of data for which access by index will be faster than temporary tables, these are a good option.
Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index it is enough to include the primary key column as the last column in the unique restriction. If you do not have a column unique, you can use an identity column.) SQL 2014 contains non-unique indexes too.
Table variables do not participate in transactions, logging or locking (logging or locking). This means that they are faster, since they do not require overload, but on the contrary they do not give you those characteristics. So for example, if you want to restore (ROLLBACK) to half way through a procedure, then the variables of Table populated during that transaction will continue to be populated!
Temporary tables could result in Stored Procedures recompiled. The 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 dealing with the change of data types through the time, since it is not necessary to define the structure of your table temporary in advance.
You can pass table variables by returning functions, which makes it much easier to encapsulate and reuse logic (for example, example, make a function to divide a string into a table values with some arbitrary delimiter).
Using the table variables within the user-defined functions (user-defined functions) allows those functions that will be used more widely (see CREATE FUNCTION documentation for more details). If you write a function you must use the table variables on temporary tables unless there is a imperative need.
Both table variables and temporary tables are stored in tempdb. But the table variables (from 2005) by default to "collation" of the current databases compared to tables that have the default "collation" of tempdb ( reference ). This means that details of "Collation" if temporary tables are used and your "collation" is different from 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 for all sessions and users.
Some additional readings:
Great response from Martin Smith at dba.stackexchange.com
MSDN FAQ about the difference between the two: link
article in the MDSN blog: link
Article: link
DECLARE @tmp TABLE (Col1 INT, Col2 INT)
Since the SQL SERVER 2008 version, the TABLE type variable is used, they are only valid during the execution of the code block.
CREATE TABLE #tmp (Col1 INT, Col2 INT)
These tables are created in the tempdb database, in the folder called Temporary Tables. if we close the current connection, this table will be deleted.
Table-type variables are only valid for a shorter time, only during the code block that is being executed.
There are temporary tables local and global that are only valid during the connection or connections that were opened.
In practice after trial and error to get more speed, at a general level you should:
Use variable tables @tabla for when the data is few columns and rows, ex. max. 10 columns and less than 10,000 records, and you do not have to make changes ( update , delete , insert ) to the table. Remember that it is stored in RAM, so it will depend on your server.
Use temporary tables #tabla or ##tabla (global, ie in all sessions), when you have thousands of records and / or need to update data in the table. Sometimes when you create it, if you have more than 10,000 rows, it is very efficient to create a primary index PK , and / or an optimized index for a query on the data.
Try both options and measure performance, as the case will give you the answer that is most efficient for your particular case, it is relatively easy to change the result of a select into @mitabla to select into #mitabla .