Sql Server: Implementing a multi-table search

Question

I'm trying to figure out the best way to search a number of different tables for a simple term. I have two solutions in mind

1) Create a "Search table" with the following structure and create triggers on the tables I would like to search to enter the information into the search table

ID || String to check against || Table || TableId =================================================================

2) Create a massive stored procedure to search the tables and columns I care about.

I'll be using this solution with Entity Framework for an Asp.Net MVC website, so I'm leaning towards the first solution for now.

Which solution makes more sense/is better?

Answer 1

SQL Server Full Text Search (FTS):

• Highly optimized
• Supports multiple
• languages query full text catalogs on multiple servers
• index xml type many
• more features

SQL Server FTS consists of several components:

• Gatherer
• Indexer
• Filter Manager
• Filter Daemon
• Full-Text Catalog

If you limit the number of outputs, SQL Server FTS really works well.

Answer 2

Option 1 has a lot of overhead and duplicates the data - a big union view would be better:

SELECT 'Table1', Table1_ID as Table_ID, Col1 as SearchCol
FROM Table1
UNION ALL
SELECT 'Table2', Table2_ID as Table_ID, Col3 as SearchCol
FROM Table2
etc

You could even make it a indexed view if you need better performance.