SQL/Server has 1 tmp database/tablespace shared by all users.
You cannot specify a different database for a given user.
Same concept as Oracle temp area, but far more used.
The SQL/Server version of "create table as select" is "select into".
When you use "select into" it locks the catalog of the database that the table is being created, which then keeps all other users from creating any tables in that particular database.
If you create your own tmp table via the "select into" syntax (very common), it prohibits all users from creating tmp tables until done, which can be a VERY LONG time.
Since joins, group bys, order bys, etc all can create tmp tables, the entire SQL/Server system grinds to a halt.
AAARRRRRGGGGGGG!!!!!!!