最详细的临时表，表变量的对比

ゝ一纸荒年。 2021-12-23 02:09 187阅读 0赞

<table style="margin-left:5.4pt;border-collapse:collapse;"> 
 <tbody> 
 <tr> 
 <td style="border-right:1pt solid;border-top:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Feature </td> 
 <td style="border-right:1pt solid;border-top:1pt solid;border-left-width:1pt;width:1.75in;border-bottom:1pt solid;"> Table Variable </td> 
 <td style="border-right:1pt solid;border-top:1pt solid;border-left-width:1pt;width:99pt;border-bottom:1pt solid;"> Temp Table </td> 
 <td style="border-right:1pt solid;border-top:1pt solid;border-left-width:1pt;width:298.85pt;border-bottom:1pt solid;"> Note </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Table Name </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Max 128 characters </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Max 116 characters </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Data Storage </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> In memory and TempDB </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> TempDB </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Meta Data </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> In memory </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> TempDB </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> A table variable inherits current database settings and can use the registered UDTs, user defined data types, and xml collections in the database. A temp table inherits the settings of TempDB and cannot use the types created in the user database if the same types do not exist in the TempDB. </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Scope </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Current batch </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Current session </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Temp tables created in a stored procedure (SP) can be referenced by dynamic queries in the SP, sub SPs, triggers fired by the affected tables of the SP. </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Constraints </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Allowed </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Allowed </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> For table variables, since no DDL is allowed, constraints can not be created in separate DDL statements. </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> DDL </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Not allowed </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Allowed. </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> E.g. create Index on the temp table. </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Concurrent </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Supported </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Supported </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Constraints and Indexes with explicit name in a temp table cause duplicate name error. </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Statistics </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Not supported </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Supported </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Estimated row number in execution plan for table variable is always 1 </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Parallel execution plan </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Supported only for select </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Supported </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Parallel query execution plans are not generated for queries that modify table variables. </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Transaction and Locking </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Not participated </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Participated </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Data in table variable is not affected if the transaction is rolled back </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Cause Recompile </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> No </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Yes </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Temp Table creation causes SPs/batches to recompile </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> SELECT INTO &lt;t&gt; </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Not supported </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Supported </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> INSERT &lt;t&gt; EXEC </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Not supported </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Supported </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-top-width:1pt;border-right:1pt solid;border-left:1pt solid;width:120.2pt;border-bottom:1pt solid;"> Use </td> 
 <td style="border-right:1pt solid;width:1.75in;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> UDFs, Stored procedures, Triggers, Batches </td> 
 <td style="border-right:1pt solid;width:99pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> Stored procedures, Triggers, Batches </td> 
 <td style="border-right:1pt solid;width:298.85pt;border-top-style:none;border-bottom:1pt solid;border-left-style:none;"> &nbsp;Temp tables can't be used in UDFs. </td> 
 </tr> 
 </tbody> 
</table>

from:http://www.sqlservercentral.com/articles/Table+Variables/63878/

Very Good~

转载于:https://www.cnblogs.com/perfectdesign/archive/2008/09/06/temp\_table\_table\_variable\_table\_diff.html