SQL Server tempdb permanent tables use cases

Question

Are there any use cases for permanent tables in tempdb database ? By permanent I mean not using # or ## but creating a real table in tempdb. I know that restarting the sql service will wipe the tempdb, but would this practice be bad for any other reason?

I'm asking because I have one use case for this, I need a temp table but that will be open for more that a sql connection span.

EDIT: the actual data in the long-living temp table are serialized files, not data based on collation or anything else

Mitch Wheat · Accepted Answer · 2012-02-20T23:52:18.523

2

Why not create a schema named 'temp' (or Staging) in your database, and create your long lived temp tables in that schema. e.g.

create schema [temp] 
     authorization [dbo]

create table [temp].[MyTempTable]
(
   Id int,
   [name] varchar(50),
   ... SomeColumns ...
)

Update: in response to your extra information, can you use FILESTREAM? (SQL Server 2008 onwards)

edited Feb 20 '12 at 23:52

answered Feb 20 '12 at 10:46

Mitch Wheat

295,962
43
465
541

because the data in the temp table will be used for file transfers from server through network (serialize, then read bytes with sql select), and by files I actually mean the database backups. – Denis Biondic Feb 20 '12 at 10:51

score 1 · Answer 2 · answered Feb 20 '12 at 10:52

1

The idea is not good for many reasons, for example - if collations of your DB and tempdb (server) does not match - the implicit string comparisons may fail

answered Feb 20 '12 at 10:52

Oleg Dok

21,109
4
45
54

score 1 · Answer 3 · answered Feb 20 '12 at 11:11

1

why does it have to be exactly on the tempdb? why not, besides all the other alternatives posted, create a shared DB to hold this table?

answered Feb 20 '12 at 11:11

Diego

34,802
21
91
134

Because tempdb already exists beside the main db for all the customers, creating another database just for this seems like a lot of overhead – Denis Biondic Feb 20 '12 at 12:09
And that overhead becomes even bigger when scaling out to multiple SQL Server instances. – Ruud Helderman Oct 19 '21 at 13:45

score 1 · Answer 4 · answered Feb 20 '12 at 13:39

The main reason I'd suggest a different approach is because tempdb is already a bottleneck because of the number of uses that SQL Server puts to it. I wouldn't suggest finding yet another reason to plug stuff into tempdb.

Other than that, tempdb is as good as any other location with the added benefit that after a service restart anything you left in there will be cleaned out.

SQL Server tempdb permanent tables use cases

4 Answers4