CODEWITHSUNDEEP
sql-servertemp-tables
Jeremy Cantrell
Jeremy Cantrell

Reputation: 27424

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?

Upvotes: 3

Views: 2239

Answers (2)

Martin Smith
Martin Smith

Reputation: 453898

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!*/

Upvotes: 5

RichardTheKiwi
RichardTheKiwi

Reputation: 107816

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.

Upvotes: 2

Related Questions