A movement is afoot in my place of employ to move away from using #temp tables and instead to use permanent physical tables with SPIDs. Whenever one would have previously INSERTed INTO a #temp table, now an
INSERT INTO dbo.MyPermanentTable (SPID, ...) VALUES (@@SPID, ...) is required - together with a bunch of
DELETE FROM dbo.MyPermanentTable WHERE SPID = @@SPID statements at the beginning of e.g. a stored procedure. Additionally, it goes without saying that anywhere that these 'permanent tables for storing temporary data' are used, one has to be careful to include a
WHERE SPID = @@SPID.
The logic behind the move towards this practice is that it'll improve overall performance of the server on which the queries are running (by reducing I/O and contention in tempdb). I'm not keen on this approach for a number of reasons - it's ugly, potentially dangerous and seems like it may well harm the performance of those queries that use the new scheme.
Does anyone have any experience with this or similar approaches to eliminating #temp tables?