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


  • 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)