Fully Qualified Query Accross Databases

Are there any perfmonace or query optimization limitations or issues that arise when issueing a fully qualified query across multiple databases on the same Instance of SQL Server. In other words are all features of query optimization fully supported in queries that span databases on the same Instance.

The only limitations that I'm aware of are the same limitations if the queries only run against one database, i.e. the databases have to be properly optimized for performance. If one database is properly indexed and one is not the bottleneck will be the database that isn't properly indexed.
|||

That is a fact, but I am really trying to determine if there are any optimizer limitations or nuances like there are if you are using partitioning.

|||The fact that there may be partitioning really isn't relevant since partitioning is only undertaken to optimize query performance anyway by distributing data across multiple files. As I mentioned, if the databases are optimized, whether through the use of partitioning or any other strategy, then cross database queries aren't an issue. There aren't any optimizer limitations or nuances that need to be considered.

If you were performing distributed queries across multiple linked servers or across multiple resource engines, i.e. SQL Server/Oracle then there might be some things to consider but even then, the issue would probably still come down to whether the databases are properly optimized.