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's the difference between a temp table and table variable in SQL Server?
There are some differences between temporary tables (#tmp) and variables (@tmp), although the use of tempdb is not one of them, as discussed in the MSDN link below.
As a general rule, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is too broad a guideline of course plenty of exceptions - see below and following articles.)
Some points to consider when choosing between them:
Some further reading:
Great answer from Martin Smith on dba.stackexchange.com
MSDN FAQ about the difference between the two: http://support.microsoft.com/default.aspx/kb/305977
MDSN blog article: 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#
Since the SQL SERVER 2008 version, the TABLE type variable is used, they are only valid during the execution of the code block.
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 variables are only valid for a short time, only during the block of code being executed.
There are local and global temporary tables that are only in effect during the connection or connections that were opened.
In practice, after trial and error to obtain greater speed, at a general level you should:
Use variable tables
@tabla
for when the data is few columns and rows, eg. max. 10 columns and less than 10,000 records, and you don't have to make changes (update
,delete
,insert
) to the table. Remember that it is stored in RAM memory, therefore it will depend on your server.Use temporary tables
#tabla
or##tabla
(global, that is, 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 indexPK
, and/or an index optimized for a query on the data.Try both options and measure performance, depending on the case, it will give you the answer that is more efficient for your particular case, it is relatively easy to change the result from a
select into @mitabla
toselect into #mitabla
.From a humble opinion in professional practice, hoping it helps you...
1) With the table as a variable and a data amount of 20,000 records my
temdb
=> began to inflateDECLARE @vCodigos TABLE (Codigo1 varchar(50), Codigo2 varchar(50))
2) Using a temporary table with the same records began to inflate me,
temdb
although to a lesser extent3) and using the WITH, it was much better and more efficient without affecting the
tempdb
=>WITH tblCodigos AS (SELECT A.codigo AS Codigo1, B.codigo2)