Sql-server – Pros/Cons of using multiple databases vs using a single database

I think you are correct in your answers and JNK is correct about locks. I see some reasons to have multiple databases.

1. Replication. If parts of your database should be replicated to local read servers they benefit from being on different database. I don't know about sql-server specifically but in mysql (and generally) a master can only maintain one binary log per database and if table A and B should replicate to some server and table C and D should replicate to another server than those needs to either be on different databases or the binary log needs to be filtered on the slaves.
2. Uptime and accessibility. If different parts of your data have different requirements on uptime and accessibility it would make sense to put them on different databases. One could have hot standby with all data replicated and the other could have a nightly dump.

But again, I think you're right :)

The columns you are talking about occupy 20 bytes (if aligned without padding):

creation time, update time and creation source

timestamp .. 8 bytes
timestamp .. 8 bytes
integer .. 4 bytes

The tuple header and item identifier for a separate row in a separate table alone would occupy 23 + 1 + 4 = 28 bytes plus the 20 bytes of actual data, plus 4 bytes of padding at the end. Makes 52 bytes per row. See:

• Configuring PostgreSQL for read performance

Concerning storage you have nothing to gain. Concerning performance you hardly lose anything with just 16 - 24 bytes more per row.

The columns also directly belong to the row, so it makes sense to keep them together. I make it a habit to add exactly such columns (plus separate source for the last update) to all relevant tables.

It's also easier to write a TRIGGER ON INSERT OR UPDATE to keep them current.

Long story short: a strong vote for your option 1.

Where I would go for option 3:
If the metadata is updated often, while the core row is not. Then it might pay to keep a separate 1:1 table to make UPDATEs cheaper and reduce bloat on the main table - or even go for option 2.

Where I would go for option 2:
If the set of metadata columns is highly repetitive. You could have a FK column to the set of metadata in the main table(s). Does not save much for three small columns like in your example.