普通表还是全局临时表? [英] Normal table or global temp table?

问题描述

我和另一位开发人员正在讨论哪种类型的表格更适合我们的任务.它基本上将成为我们将在一天结束时截断的缓存.就个人而言，我认为没有任何理由为此使用普通表以外的任何其他东西，但他想使用全局临时表.

Me and another developer are discussing which type of table would be more appropriate for our task. It's basically going to be a cache that we're going to truncate at the end of the day. Personally, I don't see any reason to use anything other than a normal table for this, but he wants to use a global temp table.

两者有什么优势吗?

推荐答案

在 tempdb 中使用普通表，如果这只是临时数据，您可以承受在服务重启或用户数据库时丢失数据不是那么短暂.

Use a normal table in tempdb if this is just transient data that you can afford to lose on service restart or a user database if the data is not that transient.

tempdb 在日志记录要求方面稍微更有效.

tempdb is slightly more efficient in terms of logging requirements.

一旦所有引用连接创建表的连接关闭，全局临时表就会被删除.

Global temp tables get dropped once all referencing connections are the connection that created the table is closed.

遵循@cyberkiwi 的编辑.BOL 确实明确明确表示

Following @cyberkiwi's edit. BOL does definitely explicitly say

全局临时表可见任何用户和他们之后的任何连接创建，并在所有时删除引用表的用户与 SQL 实例断开连接服务器.

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

在我的测试中，我也无法得到这种行为.

In my test I wasn't able to get this behaviour though either.

CREATE TABLE ##T (i int)
INSERT INTO ##T values (1)
SET CONTEXT_INFO 0x01

连接 2

INSERT INTO ##T VALUES(4)
WAITFOR DELAY '00:01'
INSERT INTO ##T VALUES(5)

连接 3

SELECT OBJECT_ID('tempdb..##T') 
declare @killspid varchar(10) = (select 'kill ' +  cast(spid as varchar(5)) from sysprocesses where context_info=0x01)
exec (@killspid)
SELECT OBJECT_ID('tempdb..##T') /*NULL - But 2 is still 
                                 running let alone disconnected!*/

这篇关于普通表还是全局临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！