临时表存储性能对比
任何人都可以用简单的英语分解它在 MSSQL 中使用临时表与 CTE 与表变量之间的性能差异。我经常使用临时表，并开始使用 CTE 只是因为语法清晰，但我发现它们速度较慢。我认为临时表正在使用系统内存，这就是为什么它们看起来很快，但如果尝试执行多项作业，可能会成为瓶颈。我很少使用表变量，而且了解不多。寻求大师的一些建议！
Can anyone break it down in plain English the performance difference between using temp tables vs. CTE's vs. table variables in MSSQL. I have used temporary tables quite frequently and have started using CTE's just because of the clear syntax but I have found them to be slower. I think that temp tables are using system memory and that is why they seem fast but may be a bottleneck if trying to do multiple jobs. Table variables I have used sparingly and do not know a great deal about. Looking for some advice from the guru's out there!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在线图书、MSDN 和本网站详细介绍了这个问题。
关于临时表和表变量，您可以在此处阅读 SQL Server 中的临时表和表变量有什么区别？。
在那里您会发现，在许多情况下，临时表会导致过程重新编译，这是它们的主要缺点。
CTE 在这里有很好的描述 http ://blogs.msdn.com/b/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx
This question is well covered in Books Online, MSDN and this site.
About temp tables and table variables you can read here What's the difference between a temp table and table variable in SQL Server?.
There you will find that in many cases temp tables cause recompilation of a procedure which is their main disadvantage.
CTEs are well described here http://blogs.msdn.com/b/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx
CTE 与性能无关。它们通过抽象 SQL 语句（通常是复杂的 JOIN 或应用于字段的内置函数）来简化开发人员的查询。数据库引擎只是将 CTE 内联到使用它的查询中。因此，CTE 本身并不“慢”，但您可能会发现临时表的性能更好，因为数据库引擎正在使用临时表对查询创建更好的查询计划。
CTEs are performance-neutral. They simplify a query for the developer by abstracting out SQL statements - usually complicated JOINs or built-in functions applied to fields. The database engine just in-lines the CTE into the query that uses it. So, the CTE itself isn't "slow", but you may find you are having better performance with temp tables because the database engine is creating better query plans on the queries using the temp tables.
这个问题已在此处和此处。
简而言之，这是针对不同任务的不同工具。
This question was answered here and here.
Briefly, this is a different tools fo different tasks.