Re: database design with temporary tables

On 8/29/21 2:26 PM, Adrian Klaver wrote:
> The pool is maintained, the individual connections(sessions) come and 
> go. Otherwise there would be no point to having a pool. Every time the 
> connection(session) is closed the temporary table disappears. 

Yes, you're right. And that is very convenient. I also prefer temporary 
tables with ON COMMIT DELETE ROWS so that I don't have leftovers between 
transactions. There is also pgtt extension by Giles Darold which 
emulates Oracle global temporary tables:

https://github.com/darold/pgtt#use-of-the-extension

I have never particularly liked Oracle's implementation of temporary 
tables, local or global,  so I have no problems with questions like 
that. BTW, I have compared Oracle's implementation with other 
implementations here:

https://dbwhisperer.wordpress.com/2019/03/18/private-temporary-tables-oracle-18c/

I could have used Postgres instead of the SQL Server, the comparison 
would read the same, except for the "#" character which denotes 
temporary table in SQL Server.

Regards


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com