CODEWITHSUNDEEP
sql-servertempdbtemp-tables
Denis Biondic
Denis Biondic

Reputation: 8201

SQL Server tempdb permanent tables use cases

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

Upvotes: 2

Views: 4448

Answers (4)

Mitch Wheat
Mitch Wheat

Reputation: 300559

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)

Upvotes: 2

Grant Fritchey
Grant Fritchey

Reputation: 2775

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.

Upvotes: 1

Diego
Diego

Reputation: 36146

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?

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21766

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

Upvotes: 1

Related Questions