Sql Server Full-text Search Against Document (multiple Related Tables And Field)

I have a document (in this case Invoice) structure which contains multiple tables: Invoice Header (No. (PK), Customer Name, Customer Address, ...) Invoice Lines (Invoice No. (PK),

Solution 1:

SQL Full-Text search would be most appropriate method, given your requirements of boolean search, multiple columns and tables.

The process is broken into steps, but roughly, you will need to:

1. Create a Full Text Catalog
2. Create a Full Text Index for each of the tables
3. Generate/build the index
4. Finally, using the FT (catalog) in your queries

I would highly recommend starting with the Getting Started article, it will help you understand some of the jargon, structure and how to manage and use full-text within SQL server.

Solution 2:

If you need to rank (score) or sort your search results, you should create a new table which, through an ETL process, combines all of the full-text-searchable data (invoice header, lines, comments) for your entity into 1 column. This seems to be what you're suggesting with your "Document Index" table idea.

Why combine them into 1 table? This approach results in better ranking than if you were to apply full text indexes to each existing table. The former solution produces a single rank whereas the latter will produce a different rank for each table and there is no accurate way to resolve multiple ranks (which are based on completely different scales) into 1 rank. To illustrate the differences:

--Querying1tableSELECTRANK, KEYFROMCONTAINSTABLE(DocumentIndex.*, @searchString)

--Queryingmultipletables (this results in multiple rank values which cannot be resolved into a single rank)
SELECTRANK, KEYFROMCONTAINSTABLE(InvoiceHeader.*, @searchString)

SELECTRANK, KEYFROMCONTAINSTABLE(InvoiceLines.*, @searchString)

SELECTRANK, KEYFROMCONTAINSTABLE(InvoiceHeaderComments.*, @searchString)

How can you combine them into 1 table? You will need some sort of ETL process which either runs on a schedule (which may be easier to implement but will result in lag time where your full text index is out of sync with the master tables) or gets run on demand whenever your master tables are modified (either via triggers or by hooking into an event in your data layer).