The motivation for this question is with regard to staging tables. Should staging tables be in a separate database for performance reasons? Or can you place staging tables and your destination tables all in one database (using "staging" as schema) without any performance impact on reads of your final tables?
My hunch is I don't think it matters where the staging tables are located, if they are on the same server, but I have nothing to back that up. If there is a performance hit, why?
Answer by Magnus Ahlkvist ·
What matters is where the datafiles are located. If you put them on the same physical storage, it won't matter for performance if you have staging tables in a schema in the main database or if you put it in its own database.
I'm simplifying a bit though. You will share transaction log file between the tables if they are in the same database. You will share allocation bitmap between the tables if you put them in the same db as well. But the main performance impact will be that the datafiles are placed on the same storage device.
There are other considerations however, which you should think about. Do you need to backup the staging database? On the same schedule as the main database? Will you manage to restore the main database in time if the staging schema is also in the same database?
I wouldn't recommend mixing staging tables and production or live DW tables in the same database. When I need to restore a database, I want only the necessary data to be a part of that. Staging database I can think about later, once I get production database or DW database up and running.