SQL Server tempdb永久表用例

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?

tempdb数据库中是否存在永久表的用例?所谓永久性，我的意思是不使用#或##，而是在tempdb中创建一个真正的表。我知道重新启动sql服务将会消除tempdb，但是这种做法是否会因为其他原因而变得糟糕呢?

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.

我问这个问题是因为我有一个用例，我需要一个临时表，但是它会比sql连接跨度更大。

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

编辑:长期使用的临时表中的实际数据是序列化的文件，而不是基于排序或其他内容的数据

4 个解决方案

#1

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

为什么不在数据库中创建一个名为“temp”(或分段)的模式，并在该模式中创建长期的临时表。如。

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)

更新:为了响应您的额外信息，您可以使用FILESTREAM吗?(SQL Server 2008起)

#2

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

这个想法并不好，原因有很多，例如，如果数据库和tempdb(服务器)的排序不匹配，那么隐式字符串比较可能会失败

#3

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?

为什么一定要在tempdb上?为什么不创建一个共享DB来保存这个表呢?

#4

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.

我建议采用另一种方法的主要原因是，由于SQL Server对它的使用数量太多，tempdb已经成为一个瓶颈。我不会建议找另一个理由把东西塞进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.

除此之外，tempdb和其他任何位置一样好，附加的好处是，在服务重新启动之后，您留在那里的任何东西都将被清理掉。

#1