做临时表（#table）而不是持久表（table）有什么好处？
我可以想到两个主要好处：
- 避免并发问题，如果您有许多进程创建/删除表，那么当一个进程尝试创建一个已经存在的表时，您可能会遇到麻烦。
- 性能，我认为创建临时表（带有#）比常规表性能更高。
还有其他原因吗？我的理由是否有误？
I can think of two main benefits:
- Avoiding concurrency problems, if you have many processes creating/dropping tables you can get in trouble as one process tries to create an already existing table.
- Performance, I imagine that creating temporary tables (with #) is more performant than regular tables.
Is there any other reason, and is any of my reasons false?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您无法比较临时表和持久表：
您可以使用临时表来假脱机结果以进行进一步处理等。
两种类型的表之间的性能（无论哪种方式）几乎没有差异。
您不应该一直删除和创建表...任何依赖于此的应用程序都在做错事，尤其是太多的 SQL 调用。
You can't compare temporary and persistent tables:
You'd use a temp table to spool results for further processing and such.
There is little difference in performance (either way) between the two types of table.
You shouldn't be dropping and creating tables all the time... any app that relies on this is doing something wrong, not least way too many SQL calls.
(1)临时表是在SQL Server TEMPDB数据库中创建的，因此需要更多的IO资源和锁定。表变量和派生表是在内存中创建的。
(2) 对于可以使用并行性处理的大量数据，临时表通常会表现得更好，而表变量最适合用于并行性无法提供的少量数据（我使用 100 行或更少的经验法则）显着的性能改进。
(3)不能使用存储过程将数据插入表变量或派生表。例如，以下命令将起作用：INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp，而以下命令将生成错误：INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp。
(4)派生表只能从 SELECT 语句创建，但可以在 Insert、Update 或 Delete 语句中使用。
(5) 按照范围持久性的顺序，临时表的范围扩展得最远，其次是表变量，最后是派生表。
(1)Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.
(2)Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.
(3)You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.
(4)Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.
(5) In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.
1）
表变量的生命周期仅在其运行的事务期间有效。如果我们先执行 DECLARE 语句，然后尝试将记录插入到 @temp 表变量中，我们会收到错误，因为表变量已不存在。如果我们在一个事务中声明记录并将其插入@temp，然后尝试查询该表，结果是相同的。如果您注意到，我们需要针对 #temp 执行 DROP TABLE 语句。这是因为该表将一直持续到会话结束或删除为止。
2）
表变量有某些明显的限制。
-表变量不能有非聚集索引
- 不能在表变量中创建约束
- 不能在表变量列上创建默认值
- 无法针对表变量创建统计信息
- 与临时表的相似之处包括：
与临时表的相似之处包括：
- 在 tempdb 中实例化
- 可以在表变量和临时表上创建聚集索引
- 两者都记录在事务日志中
- 就像临时表和常规表一样，用户可以针对表变量执行所有数据修改语言 (DML) 查询：SELECT、INSERT、UPDATE 和 DELETE。
1)
A table variable's lifespan is only for the duration of the transaction that it runs in. If we execute the DECLARE statement first, then attempt to insert records into the @temp table variable we receive the error because the table variable has passed out of existence. The results are the same if we declare and insert records into @temp in one transaction and then attempt to query the table. If you notice, we need to execute a DROP TABLE statement against #temp. This is because the table persists until the session ends or until the table is dropped.
2)
table variables have certain clear limitations.
-Table variables can not have Non-Clustered Indexes
-You can not create constraints in table variables
-You can not create default values on table variable columns
-Statistics can not be created against table variables
-Similarities with temporary tables include:
Similarities with temporary tables include:
-Instantiated in tempdb
-Clustered indexes can be created on table variables and temporary tables
-Both are logged in the transaction log
-Just as with temp and regular tables, users can perform all Data Modification Language -(DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.