MSSQL 2005 : Full Text Catalogs

Столкнулся сегодня на работе с проблемой: поиск на production у заказчика стал работать как-то странно: выдаёт не все результаты. Думаю, ну всё, придётся копать эту кошмарную процедуру в 450 строк, которую писал не я, да ещё и с динамическим sql, курсорами и временными таблицами... Ужас ситуации заставил меня содрогнуться... Но делать было нечего. Для начала запустил профайлер и посмотрел как именно вызывается эта процедура из слоя COM объектов (да-да, в этом приложении ещё используются COM'ы, написанные на VB6). Профайлер показал мне буквально следующее:

exec groupSearch N'"word"',0,0,NULL,NULL,0,NULL,NULL,NULL,1,10,0, 
N'DECLARE @a0 NVARCHAR(999) SET @a0=N''%manifold%''',N'((A LIKE @a0)) 
OR ((B LIKE @a0)) OR ((C LIKE @a0)) OR ((D LIKE @a0)) OR ((E LIKE @a0)) 
OR ((F LIKE @a0)) OR ((G LIKE @a0)) OR ((H LIKE @a0)) OR ((I LIKE @a0)) 
OR ((J LIKE @a0)) OR ((K LIKE @a0)) OR ((L LIKE @a0)) OR ((M LIKE @a0)) 
OR ((N LIKE @a0))',68,0,0,1

Я оценил красоту кода и пошёл дальше. Теперь меня стало интересовать какой SQL генерит процедура когда её вызывают подобным образом. Я вывел его при помощи print и запустил. К своему неудовольствию я обнаружил, что вместо любимого мной оператора LIKE в запросе используется какой-то непонятный CONTAINS. Почитав справку про этот CONTAINS я понял что вещь достаточно удобная, поскольку, ему можно скормить не одно поле и даже не несколько. Можно в буквальном смысле в CONTAINS передать *, что будет означать что поиск будет осуществлён по всем полям таблицы. Однако, поэксперементировав с CONTAINS я увидел что результаты, возвращаемые им зачастую отличаются от результатов, которые возвращает LIKE. И как бы я не писал запрос, всё равно факт оставался - некоторые записи просто не попадали в выборку. Например:

select * from table where CONTAINS(Name,'"word"');
select * from table where Name like '%word%';

Первый запрос возвращал мне 3 записи, а второй все 6. В чём же дело. Сначала я подумал что проблема именно в принципе работы CONTAINS - мол, он и должен возвращаеть только записи, где искомое слово в конце или в начале текста (так получалось по результатам). Но перспектива переписывать ужасную процедуру на LIKE меня не радовала, поэтому я начал копать. Оказывается contains работатет только при включённом полнотекстовом индексировании (Full Text Search) для таблицы. А чтобы включить его, необходимо добавить в бу так называемый Full Text Catalog (полнотекстовый каталог). Активация функции полнотекстового индексирования для таблицы Microsoft SQL Server 2005 состоит из двух этапов:

1. Создание полнотекстового каталога для хранения полнотекстовых индексов.
2. Создание полнотекстовых индексов.

Чтобы создать полнотекстовый каталог AdvWksDocFTCat, нужно выполнить инструкцию CREATE FULLTEXT CATALOG:

CREATE FULLTEXT CATALOG AdvWksDocFTCat

Но удобнее сделать это через SQL Management Studio. Для этого нужно перейти к Storage->Full Text Catalogs. В этот момент я понял что полнотекстовый каталог в базе уже есть и полез смотреть его свойства. Мне показалось что всё настроено верно и нужно просто перестроить сам каталог, что я и попытался сделать. Вопреки ожиданиям (на форумах писали что rebuild идёт очень долго), rebuild прошёл моментально и я увидел, что это какой-то глюк mssql, поскольку реально он ничего не пересобрал и просто начал отображать нулевые значения (фактически он просто убил старый каталог, а создавать его заново и не подумал). Нажав на rebuild ещё несколько раз я наконец-то добился желаемого результата и каталог пересобрался. Однако, победа оказалась неокончательной - теперь запрос с CONTAINS перестал выдавать какие-либо результаты, будто каталог полнотекстового поиска был убит вовсе. Поиграв с настройками я случайно выяснил что для нормальной работы полнотекстового поиска по таблице необходимо ещё и указать Track changes: Automatically для таблиц, которые в нём нуждаются. После этого запросы с CONTAINS стали работать так же хорошо как и LIKE.