In the process of setting standards at Enterprise level in our organization. I curious what are the advantages and disadvantages:
1. Hosting all the tables under one database against spreading across different/several physical Databases.
2. Is there any performance hit when tables are joined across different databases.
There is no performance difference when joining/referencing across multiple databases.
The primary reason for using separate databases to organizae your tables is to logically group them for both ease of users finding things and for admin tasks like assigning access rights, performing backups,... Think of databases on teradata like you would directories/folders in your favorite OS.