全局临时表: ##T1
局部临时表: #T1
表变量: @T1
临时表 vs 表变量:可见性,持久性,性能(具体见下表)
临时表有统计信息可以支持优化
表变量不需要重新编译
Functionality/Object Type | Local Temp Table | Table Variable | Table Expression |
---|---|---|---|
Scope/Visibility | Current and inner levels | Local Batch | Derived Table/CTE: Current statement View/Inline UDF: Global |
Physical representation in tempdb | Yes | Yes | No |
Part of outer transaction/Affected by outer transaction rollback | Yes | No | Yes |
Logging | To support transaction rollback | To support statement rollback | Yes |
Locking | Yes | No | Yes |
Statistics/recompilations/efficient plans | Yes | No | Yes |
Table size | Any | Typically recommended for small tables | Any |