<p>In SQL Server 2005, we can create temp tables one of two ways:</p>

<pre><code>declare @tmp table (Col1 int, Col2 int);
</code></pre>

<p>or</p>

<pre><code>create table #tmp (Col1 int, Col2 int);
</code></pre>

<p>What are the differences between these two?  I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memory.  </p>

<p>In which scenarios does one out-perform the other? </p>

[{'answer_id': 27905, 'author': 'JamesSugrue', 'author_id': 1075, 'author_profile': 'https://Stackoverflow.com/users/1075', 'pm_score': 4, 'selected': False, 'text': '<p>@wcm - actually to nit pick the Table Variable isn\'t Ram only - it can be partially stored on disk.</p>\n\n<p>A temp table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variables can be faster, but obviously 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.</p>\n\n<p><a href="http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1267047,00.html#" rel="noreferrer">Good background article</a></p>\n'}, {'answer_id': 27951, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 3, 'selected': False, 'text': '<p>For all of you who believe the myth that temp variables are in memory only </p>\n\n<p>First, the table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.</p>\n\n<p>Read the article here: <a href="http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx" rel="noreferrer">TempDB:: Table variable vs local temporary table</a> </p>\n'}, {'answer_id': 27954, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 4, 'selected': False, 'text': '<blockquote>\n  <p>In which scenarios does one out-perform the other?</p>\n</blockquote>\n\n<p>For smaller tables (less than 1000 rows) use a temp variable, otherwise use a temp table.</p>\n'}, {'answer_id': 64891, 'author': 'Rory', 'author_id': 8479, 'author_profile': 'https://Stackoverflow.com/users/8479', 'pm_score': 10, 'selected': True, 'text': '<p>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.</p>\n\n<p>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.)</p>\n\n<p>Some points to consider when choosing between them: </p>\n\n<ul>\n<li><p>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.</p></li>\n<li><p>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.) <a href="https://stackoverflow.com/questions/886050/sql-server-creating-an-index-on-a-table-variable/17385085#17385085">SQL 2014 has non-unique indexes too</a>.</p></li>\n<li><p>Table variables don\'t participate in transactions and <code>SELECT</code>s are implicitly with <code>NOLOCK</code>. 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!</p></li>\n<li><p>Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.</p></li>\n<li><p>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. </p></li>\n<li><p>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).</p></li>\n<li><p>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.</p></li>\n<li><p>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 (<a href="https://learn.microsoft.com/sql/t-sql/language-elements/declare-local-variable-transact-sql" rel="noreferrer">ref</a>). 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.</p></li>\n<li><p>Global Temp Tables (##tmp) are another type of temp table available to all sessions and users. </p></li>\n</ul>\n\n<p>Some further reading:</p>\n\n<ul>\n<li><p><a href="https://dba.stackexchange.com/a/16386">Martin Smith\'s great answer</a> on dba.stackexchange.com</p></li>\n<li><p>MSDN FAQ on difference between the two: <a href="https://support.microsoft.com/en-gb/kb/305977" rel="noreferrer">https://support.microsoft.com/en-gb/kb/305977</a></p></li>\n<li><p>MDSN blog article: <a href="https://learn.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table" rel="noreferrer">https://learn.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table</a></p></li>\n<li><p>Article: <a href="https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables" rel="noreferrer">https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables</a></p></li>\n<li><p>Unexpected behaviors and performance implications of temp tables and temp variables: <a href="https://sql.kiwi/2012/08/temporary-tables-in-stored-procedures.html" rel="noreferrer">Paul White on SQLblog.com</a></p></li>\n</ul>\n'}, {'answer_id': 64968, 'author': 'HLGEM', 'author_id': 9034, 'author_profile': 'https://Stackoverflow.com/users/9034', 'pm_score': 2, 'selected': False, 'text': '<p>Consider also that you can often replace both with derived tables which may be faster as well. As with all performance tuning, though, only actual tests against your actual data can tell you the best approach for your particular query.</p>\n'}, {'answer_id': 65634, 'author': 'GilaMonster', 'author_id': 9342, 'author_profile': 'https://Stackoverflow.com/users/9342', 'pm_score': 3, 'selected': False, 'text': "<p>The other main difference is that table variables don't have column statistics, where as temp tables do. This means that the query optimiser 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.</p>\n"}, {'answer_id': 7609360, 'author': 'BrianFinkel', 'author_id': 394837, 'author_profile': 'https://Stackoverflow.com/users/394837', 'pm_score': 3, 'selected': False, 'text': "<p>Another difference:</p>\n\n<p>A table var can only be accessed from statements within the procedure that creates it, not from other procedures called by that procedure or nested dynamic SQL (via exec or sp_executesql).</p>\n\n<p>A temp table's scope, on the other hand, includes code in called procedures and nested dynamic SQL.</p>\n\n<p>If the table created by your procedure must be accessible from other called procedures or dynamic SQL, you must use a temp table.  This can be very handy in complex situations.</p>\n"}, {'answer_id': 8204184, 'author': 'Martin Smith', 'author_id': 73226, 'author_profile': 'https://Stackoverflow.com/users/73226', 'pm_score': 5, 'selected': False, 'text': '<p>Just looking at the claim in the accepted answer that table variables don\'t participate in logging. </p>\n\n<p>It seems generally untrue that there is any difference in quantity of logging (at least for <code>insert</code>/<code>update</code>/<code>delete</code> operations to the table itself though I have <a href="https://dba.stackexchange.com/a/13412/3690">since found</a> that there is some small difference in this respect for cached temporary objects in stored procedures due to additional system table updates).</p>\n\n<p>I looked at the logging behaviour against both a <code>@table_variable</code> and a <code>#temp</code> table for the following operations.</p>\n\n<ol>\n<li>Successful Insert </li>\n<li>Multi Row Insert where statement rolled back due to constraint violation.</li>\n<li>Update</li>\n<li>Delete</li>\n<li>Deallocate</li>\n</ol>\n\n<p>The transaction log records were almost identical for all operations. </p>\n\n<p>The table variable version actually has a few <strong>extra</strong> log entries because it gets an entry added to  (and later removed from) the <code>sys.syssingleobjrefs</code> base table but overall had a few less bytes logged purely as the internal name for table variables consumes 236 less bytes than for <code>#temp</code> tables (118 fewer <code>nvarchar</code> characters).</p>\n\n<h3>Full script to reproduce (best run on an instance started in single user mode and using <code>sqlcmd</code> mode)</h3>\n\n<pre><code>:setvar tablename "@T" \n:setvar tablescript "DECLARE @T TABLE"\n\n/*\n --Uncomment this section to test a #temp table\n:setvar tablename "#T" \n:setvar tablescript "CREATE TABLE #T"\n*/\n\nUSE tempdb \nGO    \nCHECKPOINT\n\nDECLARE @LSN NVARCHAR(25)\n\nSELECT @LSN = MAX([Current LSN])\nFROM fn_dblog(null, null) \n\n\nEXEC(N\'BEGIN TRAN StartBatch\nSAVE TRAN StartBatch\nCOMMIT\n\n$(tablescript)\n(\n[4CA996AC-C7E1-48B5-B48A-E721E7A435F0] INT PRIMARY KEY DEFAULT 0,\nInRowFiller char(7000) DEFAULT \'\'A\'\',\nOffRowFiller varchar(8000) DEFAULT REPLICATE(\'\'B\'\',8000),\nLOBFiller varchar(max) DEFAULT REPLICATE(cast(\'\'C\'\' as varchar(max)),10000)\n)\n\n\nBEGIN TRAN InsertFirstRow\nSAVE TRAN InsertFirstRow\nCOMMIT\n\nINSERT INTO $(tablename)\nDEFAULT VALUES\n\nBEGIN TRAN Insert9Rows\nSAVE TRAN Insert9Rows\nCOMMIT\n\n\nINSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])\nSELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT 0))\nFROM sys.all_columns\n\nBEGIN TRAN InsertFailure\nSAVE TRAN InsertFailure\nCOMMIT\n\n\n/*Try and Insert 10 rows, the 10th one will cause a constraint violation*/\nBEGIN TRY\nINSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])\nSELECT TOP (10) (10 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 20\nFROM sys.all_columns\nEND TRY\nBEGIN CATCH\nPRINT ERROR_MESSAGE()\nEND CATCH\n\nBEGIN TRAN Update10Rows\nSAVE TRAN Update10Rows\nCOMMIT\n\nUPDATE $(tablename)\nSET InRowFiller = LOWER(InRowFiller),\n    OffRowFiller  =LOWER(OffRowFiller),\n    LOBFiller  =LOWER(LOBFiller)\n\n\nBEGIN TRAN Delete10Rows\nSAVE TRAN Delete10Rows\nCOMMIT\n\nDELETE FROM  $(tablename)\nBEGIN TRAN AfterDelete\nSAVE TRAN AfterDelete\nCOMMIT\n\nBEGIN TRAN EndBatch\nSAVE TRAN EndBatch\nCOMMIT\')\n\n\nDECLARE @LSN_HEX NVARCHAR(25) = \n        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + \':\' +\n        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + \':\' +\n        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)        \n\nSELECT \n    [Operation],\n    [Context],\n    [AllocUnitName],\n    [Transaction Name],\n    [Description]\nFROM   fn_dblog(@LSN_HEX, null) AS D\nWHERE  [Current LSN] &gt; @LSN  \n\nSELECT CASE\n         WHEN GROUPING(Operation) = 1 THEN \'Total\'\n         ELSE Operation\n       END AS Operation,\n       Context,\n       AllocUnitName,\n       COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],\n       COUNT(*)                              AS Cnt\nFROM   fn_dblog(@LSN_HEX, null) AS D\nWHERE  [Current LSN] &gt; @LSN  \nGROUP BY GROUPING SETS((Operation, Context, AllocUnitName),())\n</code></pre>\n\n<p>Results</p>\n\n<pre><code>+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+\n|                       |                    |                           |             @TV      |             #TV      |                  |\n+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+\n| Operation             | Context            | AllocUnitName             | Size in Bytes | Cnt  | Size in Bytes | Cnt  | Difference Bytes |\n+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+\n| LOP_ABORT_XACT        | LCX_NULL           |                           | 52            | 1    | 52            | 1    |                  |\n| LOP_BEGIN_XACT        | LCX_NULL           |                           | 6056          | 50   | 6056          | 50   |                  |\n| LOP_COMMIT_XACT       | LCX_NULL           |                           | 2548          | 49   | 2548          | 49   |                  |\n| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 624           | 3    | 624           | 3    |                  |\n| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 208           | 1    | 208           | 1    |                  |\n| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrscols.clst        | 832           | 4    | 832           | 4    |                  |\n| LOP_CREATE_ALLOCCHAIN | LCX_NULL           |                           | 120           | 3    | 120           | 3    |                  |\n| LOP_DELETE_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 720           | 9    | 720           | 9    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.clust   | 444           | 3    | 444           | 3    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.nc      | 276           | 3    | 276           | 3    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.clst       | 628           | 4    | 628           | 4    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.nc         | 484           | 4    | 484           | 4    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.clst      | 176           | 1    | 176           | 1    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.nc        | 144           | 1    | 144           | 1    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.clst        | 100           | 1    | 100           | 1    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.nc1         | 88            | 1    | 88            | 1    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysobjvalues.clst     | 596           | 5    | 596           | 5    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrowsets.clust      | 132           | 1    | 132           | 1    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrscols.clst        | 528           | 4    | 528           | 4    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.clst       | 1040          | 6    | 1276          | 6    | 236              |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc1        | 820           | 6    | 1060          | 6    | 240              |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc2        | 820           | 6    | 1060          | 6    | 240              |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc3        | 480           | 6    | 480           | 6    |                  |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.clst | 96            | 1    |               |      | -96              |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.nc1  | 88            | 1    |               |      | -88              |\n| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | Unknown Alloc Unit        | 72092         | 19   | 72092         | 19   |                  |\n| LOP_DELETE_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 16348         | 37   | 16348         | 37   |                  |\n| LOP_FORMAT_PAGE       | LCX_HEAP           | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  |\n| LOP_FORMAT_PAGE       | LCX_IAM            | Unknown Alloc Unit        | 252           | 3    | 252           | 3    |                  |\n| LOP_FORMAT_PAGE       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 84            | 1    | 84            | 1    |                  |\n| LOP_FORMAT_PAGE       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 4788          | 57   | 4788          | 57   |                  |\n| LOP_HOBT_DDL          | LCX_NULL           |                           | 108           | 3    | 108           | 3    |                  |\n| LOP_HOBT_DELTA        | LCX_NULL           |                           | 9600          | 150  | 9600          | 150  |                  |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 456           | 3    | 456           | 3    |                  |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syscolpars.clst       | 644           | 4    | 644           | 4    |                  |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysidxstats.clst      | 180           | 1    | 180           | 1    |                  |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysiscols.clst        | 104           | 1    | 104           | 1    |                  |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysobjvalues.clst     | 616           | 5    | 616           | 5    |                  |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 136           | 1    | 136           | 1    |                  |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrscols.clst        | 544           | 4    | 544           | 4    |                  |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1064          | 6    | 1300          | 6    | 236              |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syssingleobjrefs.clst | 100           | 1    |               |      | -100             |\n| LOP_INSERT_ROWS       | LCX_CLUSTERED      | Unknown Alloc Unit        | 135888        | 19   | 135888        | 19   |                  |\n| LOP_INSERT_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  |\n| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysallocunits.nc      | 288           | 3    | 288           | 3    |                  |\n| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syscolpars.nc         | 500           | 4    | 500           | 4    |                  |\n| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysidxstats.nc        | 148           | 1    | 148           | 1    |                  |\n| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysiscols.nc1         | 92            | 1    | 92            | 1    |                  |\n| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc1        | 844           | 6    | 1084          | 6    | 240              |\n| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc2        | 844           | 6    | 1084          | 6    | 240              |\n| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc3        | 504           | 6    | 504           | 6    |                  |\n| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syssingleobjrefs.nc1  | 92            | 1    |               |      | -92              |\n| LOP_INSERT_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 5112          | 71   | 5112          | 71   |                  |\n| LOP_MARK_SAVEPOINT    | LCX_NULL           |                           | 508           | 8    | 508           | 8    |                  |\n| LOP_MODIFY_COLUMNS    | LCX_CLUSTERED      | Unknown Alloc Unit        | 1560          | 10   | 1560          | 10   |                  |\n| LOP_MODIFY_HEADER     | LCX_HEAP           | Unknown Alloc Unit        | 3780          | 45   | 3780          | 45   |                  |\n| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.syscolpars.clst       | 384           | 4    | 384           | 4    |                  |\n| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysidxstats.clst      | 100           | 1    | 100           | 1    |                  |\n| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysrowsets.clust      | 92            | 1    | 92            | 1    |                  |\n| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1144          | 13   | 1144          | 13   |                  |\n| LOP_MODIFY_ROW        | LCX_IAM            | Unknown Alloc Unit        | 4224          | 48   | 4224          | 48   |                  |\n| LOP_MODIFY_ROW        | LCX_PFS            | Unknown Alloc Unit        | 13632         | 169  | 13632         | 169  |                  |\n| LOP_MODIFY_ROW        | LCX_TEXT_MIX       | Unknown Alloc Unit        | 108640        | 120  | 108640        | 120  |                  |\n| LOP_ROOT_CHANGE       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 960           | 10   | 960           | 10   |                  |\n| LOP_SET_BITS          | LCX_GAM            | Unknown Alloc Unit        | 1200          | 20   | 1200          | 20   |                  |\n| LOP_SET_BITS          | LCX_IAM            | Unknown Alloc Unit        | 1080          | 18   | 1080          | 18   |                  |\n| LOP_SET_BITS          | LCX_SGAM           | Unknown Alloc Unit        | 120           | 2    | 120           | 2    |                  |\n| LOP_SHRINK_NOOP       | LCX_NULL           |                           |               |      | 32            | 1    | 32               |\n+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+\n| Total                 |                    |                           | 410144        | 1095 | 411232        | 1092 | 1088             |\n+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+\n</code></pre>\n'}, {'answer_id': 16298637, 'author': 'Kumar Manish', 'author_id': 1051234, 'author_profile': 'https://Stackoverflow.com/users/1051234', 'pm_score': 4, 'selected': False, 'text': "<ol>\n<li><p>Temp table: A Temp table is easy to create and back up data.</p>\n<p>Table variable: But the table variable involves the effort when we usually create the normal tables.</p>\n</li>\n<li><p>Temp table: Temp table result can be used by multiple users.</p>\n<p>Table variable: But the table variable can be used by the current user only.\xa0</p>\n</li>\n<li><p>Temp table: Temp table will be stored in the tempdb. It will make network traffic. When we have large data in the temp table then it has to work across the database. A Performance issue will exist.</p>\n<p>Table variable: But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb.</p>\n</li>\n<li><p>Temp table: Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc..,</p>\n<p>Table variable: Whereas table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only.</p>\n</li>\n<li><p>Temp table: Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table.</p>\n<p>Table variable: But the table variable can be used up to that program. (Stored procedure)</p>\n</li>\n<li><p>Temp table: Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions.</p>\n<p>Table variable: But we cannot do it for table variable.</p>\n</li>\n<li><p>Temp table: Functions cannot use the temp variable. More over we cannot do the DML operation in the functions .</p>\n<p>Table variable: But the function allows us to use the table variable. But using the table variable we can do that.</p>\n</li>\n<li><p>Temp table: The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls.</p>\n<p>Table variable: Whereas the table variable won't do like that.</p>\n</li>\n</ol>\n"}, {'answer_id': 32400833, 'author': 'Teoman shipahi', 'author_id': 929902, 'author_profile': 'https://Stackoverflow.com/users/929902', 'pm_score': 3, 'selected': False, 'text': '<p>Quote taken from; <a href="https://rads.stackoverflow.com/amzn/click/com/1118177657" rel="noreferrer" rel="nofollow noreferrer">Professional SQL Server 2012 Internals and Troubleshooting</a></p>\n\n<blockquote>\n  <p><strong>Statistics</strong>\n  The major difference between temp tables and table variables is that\n  statistics are not created on table variables. This has two major\n  consequences, the fi rst of which is that the Query Optimizer uses a\n  fi xed estimation for the number of rows in a table variable\n  irrespective of the data it contains. Moreover, adding or removing\n  data doesn’t change the estimation.</p>\n  \n  <p><strong>Indexes</strong> You can’t create indexes on table variables although you can\n  create constraints. This means that by creating primary keys or unique\n  constraints, you can have indexes (as these are created to support\n  constraints) on table variables. Even if you have constraints, and\n  therefore indexes that will have statistics, the indexes will not be\n  used when the query is compiled because they won’t exist at compile\n  time, nor will they cause recompilations.</p>\n  \n  <p><strong>Schema Modifications</strong> Schema modifications are possible on temporary\n  tables but not on table variables. Although schema modifi cations are\n  possible on temporary tables, avoid using them because they cause\n  recompilations of statements that use the tables.</p>\n</blockquote>\n\n<p><a href="https://i.stack.imgur.com/gkH9V.png" rel="noreferrer"><img src="https://i.stack.imgur.com/gkH9V.png" alt="Temporary Tables versus Table Variables"></a></p>\n\n<p>TABLE VARIABLES ARE NOT CREATED IN MEMORY</p>\n\n<p><strong>There is a common misconception that table variables are in-memory structures\nand as such will perform quicker than temporary tables</strong>. Thanks to a DMV\ncalled  sys . dm _ db _ session _ space _ usage , which shows tempdb usage by\nsession, <strong>you can prove that’s not the case</strong>. After restarting SQL Server to clear the\nDMV, run the following script to confi rm that your  session _ id returns  0 for\nuser _ objects _ alloc _ page _ count :</p>\n\n<pre><code>SELECT session_id,\ndatabase_id,\nuser_objects_alloc_page_count\nFROM sys.dm_db_session_space_usage\nWHERE session_id &gt; 50 ;\n</code></pre>\n\n<p>Now you can check how much space a temporary table uses by running the following\n    script to create a temporary table with one column and populate it with one row:</p>\n\n<pre><code>CREATE TABLE #TempTable ( ID INT ) ;\nINSERT INTO #TempTable ( ID )\nVALUES ( 1 ) ;\nGO\nSELECT session_id,\ndatabase_id,\nuser_objects_alloc_page_count\nFROM sys.dm_db_session_space_usage\nWHERE session_id &gt; 50 ;\n</code></pre>\n\n<p>The results on my server  indicate that the table was allocated one page in tempdb.\nNow run the same script but use a table variable\nthis time:</p>\n\n<pre><code>DECLARE @TempTable TABLE ( ID INT ) ;\nINSERT INTO @TempTable ( ID )\nVALUES ( 1 ) ;\nGO\nSELECT session_id,\ndatabase_id,\nuser_objects_alloc_page_count\nFROM sys.dm_db_session_space_usage\nWHERE session_id &gt; 50 ;\n</code></pre>\n\n<p><strong>Which one to Use?</strong></p>\n\n<blockquote>\n  <p>Whether or not you use temporary tables or table variables should be\n  decided by thorough testing, but <strong>it’s best to lean towards temporary</strong>\n  <strong>tables as the default because there are far fewer things that can go</strong>\n  <strong>wrong</strong>.  </p>\n  \n  <p>I’ve seen customers develop code using table variables because  they\n  were dealing with a small amount of rows, and it was quicker than  a\n  temporary table, but a few years later there were hundreds of \n  thousands of rows in the table variable and performance was terrible, \n  so try and allow for some capacity planning when you make your \n  decision!</p>\n</blockquote>\n'}, {'answer_id': 58875211, 'author': 'Litisqe Kumar', 'author_id': 5047627, 'author_profile': 'https://Stackoverflow.com/users/5047627', 'pm_score': 2, 'selected': False, 'text': '<p>Differences between <code>Temporary Tables (##temp/#temp)</code> and <code>Table Variables (@table)</code> are as:</p>\n<ol>\n<li><p><code>Table variable (@table)</code> is created in the <code>memory</code>. Whereas, a <code>Temporary table (##temp/#temp)</code> is created in the <code>tempdb database</code>. However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb.</p>\n</li>\n<li><p><code>Table variables</code> cannot be involved in <code>transactions, logging or locking</code>. This makes <code>@table faster then #temp</code>. So table variable is faster then temporary table.</p>\n</li>\n<li><p><code>Temporary table</code> allows Schema modifications unlike <code>Table variables</code>.</p>\n</li>\n<li><p><code>Temporary tables</code> are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine.</p>\n</li>\n<li><p><code>Temporary tables</code> are allowed <code>CREATE INDEXes</code> whereas, <code>Table variables</code> aren’t allowed <code>CREATE INDEX</code> instead they can have index by using <code>Primary Key or Unique Constraint</code>.</p>\n</li>\n</ol>\n'}, {'answer_id': 60214373, 'author': 'Weihui Guo', 'author_id': 4271117, 'author_profile': 'https://Stackoverflow.com/users/4271117', 'pm_score': 3, 'selected': False, 'text': '<p>It surprises me that no one mentioned the key difference between these two is that the temp table supports <strong>parallel insert</strong> while the table variable doesn\'t. You should be able to see the difference from the execution plan. And here is <a href="https://channel9.msdn.com/Series/SQL-Workshops/Parallel-Insert-Into-Table-Variable-Vs-Temporary-Table-in-SQL-Server" rel="nofollow noreferrer">the video from SQL Workshops on Channel 9</a> and <a href="https://learn.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql?redirectedfrom=MSDN&amp;view=sql-server-ver16#limitations-and-restrictions" rel="nofollow noreferrer">MSDN doc</a>.</p>\n<p>This also explains why you should use a table variable for smaller tables, otherwise a temp table, as <a href="https://stackoverflow.com/a/27954/4271117">SQLMenace answered</a> before.</p>\n'}, {'answer_id': 70033739, 'author': 'satheesh', 'author_id': 17242923, 'author_profile': 'https://Stackoverflow.com/users/17242923', 'pm_score': 1, 'selected': False, 'text': '<p>In SQL the Temporary tables are stored in the TempDB and the local temporary tables are only visible in the current session and it will not be visible in another session. This can be shared between nested stored procedure calls. The Global temporary tables are visible to all other sessions and they are destroyed when the last connection referencing table is closed. For Example,</p>\n<pre><code>Select Dept.DeptName, Dept.DeptId, COUNT(*) as TotalEmployees\ninto #TempEmpCount\nfrom Tbl_EmpDetails Emp\njoin Tbl_Dept Dept\non Emp.DeptId = Dept.DeptId\ngroup by DeptName, Dept.DeptId\n</code></pre>\n<p>Table variables are similar to tempTables, a table variable is also created in TempDB. The scope of a table variable is the batch, stored procedure, or statement block in which it is declared. They can be passed as parameters between procedures. The same query can be written using Table variable by</p>\n<pre><code>Declare @tblEmployeeCount table\n(DeptName nvarchar(20),DeptId int, TotalEmployees int)\nInsert @tblEmployeeCount\nSelect DeptName, Tbl_Dept.DeptId, COUNT(*) as TotalEmployees\nfrom Tbl_EmpDetails\njoin Tbl_Dept\non Tbl_EmpDetails.DeptId = Tbl_Dept.DeptId\ngroup by DeptName, Tbl_Dept.DeptId\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/27894', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1219/']