Full-text Indexing for a view with multiple databases

Question

Can MS SQL support full-text indexing for a view that connects (joins or unions) multiple databases?

Answer 1

Yes, absolutely. Each index will be queried individually and the results will be combined by the engine.

For example, if you've got:

• DatabaseA, TableA, FieldA with a full text index
• DatabaseB, TableB, FieldB with a full text index

And you have a view that includes both fields from both tables in both databases, it'll work fine when you query that view. From SQL Server's perspective, it doesn't matter whether they're in the same database or not.

If that doesn't match your scenario, try posting more detail about your challenges. Thanks!

Answer 2

No, not at all.

You cannot create a full text index on a table or view without an index.

You cannot create a view with a clustered index that contains Left/right joins or Unions.

You can do a full text search on a view that contains data from another database, but only if it contains a single table or inner joined tables.