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.
开发者_高级运维Are there any advantages to one or the other?
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
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.
Edit: Following @cyberkiwi's edit. BOL does definitely explicitly say
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.
Connection 1
CREATE TABLE ##T (i int)
INSERT INTO ##T values (1)
SET CONTEXT_INFO 0x01
Connection 2
INSERT INTO ##T VALUES(4)
WAITFOR DELAY '00:01'
INSERT INTO ##T VALUES(5)
Connection 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!*/
Global temp table
- -ve: As soon as the connection
that created
the table goes out of scope, it takes the table with it. This is damaging if you use connection pooling which can swap connections constantly and possibly reset it - -ve: You need to keep checking to see if the table already exists (after restart) and create it if not
- +ve: Simple logging in tempdb reduces I/O and CPU activity
Normal table
- +ve: Normal logging keeps your cache with your main db. If your "cache" is maintained but is still mission critical, this keeps it consistent together with the db
- -ve: follow from above More logging
- +ve: The table is always around, and for all connections
If the cache is a something like a quick lookup summary for business/critical data, even if it is reset/truncated at the end of the day, I would prefer to keep it a normal table in the db proper.
精彩评论