Полнотекстовый поиск в SQL Server по документу (несколько связанных таблиц и полей)

Question

У меня есть структура документа (в данном случае Invoice), которая содержит несколько таблиц:

• Invoice Header (№ (PK), имя клиента, адрес клиента,...)

• Invoice Lines (Счет-фактура № (PK), строка № (PK), описание, кол-во,...)

• Invoice Header Comments (Счет № (PK), Комментарий № (PK), Комментарий)

Когда я запускаю поиск, я хотел бы выполнить его по всему документу (как по одному объекту, а не по отдельным полям (Имя клиента + Адрес клиента + Описание + Комментарий).

Пример: все документы, имеющие отношение к "Велосипеду И Берлину" или "Мюнхену или Берлину" или "Быстрая доставка"....

Какой подход вы бы порекомендовали для решения этой проблемы?

Должен ли я создать отдельную таблицу индексов для хранения объединенных значений из всех полей, которые я хотел бы проиндексировать (имя клиента, адрес клиента, описание, комментарий) - по одной строке на документ:

Индекс документа (№ документа (PK), Индекс). В таком случае, как мне обновлять таблицу "Индекс документа"?

Я пытался создать индексированные представления, которые объединяют значения, но получил ограничение - индексированное представление не может содержать вложенные элементы или использовать другие представления.

Буду признателен за все идеи.

Answer 1

Если вам нужно ранжировать (оценивать) или сортировать результаты поиска, вы должны создать новую таблицу, которая в процессе ETL объединяет все полнотекстовые данные для поиска (заголовок счета, строки, комментарии) для вашего объекта в 1 колонка. Похоже, это то, что вы предлагаете с идеей таблицы "Индекс документа".

Зачем объединять их в 1 таблицу? Этот подход приводит к лучшему ранжированию, чем если бы вы применяли полнотекстовые индексы к каждой существующей таблице. Первое решение дает один ранг, тогда как второе будет создавать разные ранги для каждой таблицы, и нет точного способа преобразования нескольких рангов (основанных на совершенно разных масштабах) в один ранг. Чтобы проиллюстрировать различия:

-- Querying 1 table
SELECT RANK, KEY FROM CONTAINSTABLE(DocumentIndex.*, @searchString)

-- Querying multiple tables (this results in multiple rank values which cannot be resolved into a single rank)
SELECT RANK, KEY FROM CONTAINSTABLE(InvoiceHeader.*, @searchString)

SELECT RANK, KEY FROM CONTAINSTABLE(InvoiceLines.*, @searchString)

SELECT RANK, KEY FROM CONTAINSTABLE(InvoiceHeaderComments.*, @searchString)

Как вы можете объединить их в 1 таблицу? Вам понадобится какой-то процесс ETL, который либо выполняется по расписанию (что может быть проще для реализации, но приведет к задержке, когда ваш полнотекстовый индекс не синхронизирован с основными таблицами), либо запускается по требованию, когда ваши основные таблицы изменяются (либо с помощью триггеров, либо путем подключения к событию на уровне данных).

Answer 2

Полнотекстовый поиск SQL будет наиболее подходящим методом, учитывая ваши требования логического поиска, нескольких столбцов и таблиц.

Процесс разбит на шаги, но примерно вам необходимо:

1. Создать полнотекстовый каталог
2. Создайте полнотекстовый индекс для каждой из таблиц
3. Создать / построить индекс
4. Наконец, используя FT (каталог) в ваших запросах

Я настоятельно рекомендую начать со статьи " Приступая к работе", она поможет вам понять некоторые жаргоны, структуру и способы управления и использования полнотекстового кода на сервере SQL.