SQL Server tempdb永久表用例

时间:2021-11-14 17:16:43

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


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.

为什么不在数据库中创建一个名为“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


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

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

#3


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?

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

#4


1  

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


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.

为什么不在数据库中创建一个名为“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


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

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

#3


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?

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

#4


1  

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和其他任何位置一样好,附加的好处是,在服务重新启动之后,您留在那里的任何东西都将被清理掉。