Настройка полнотекстового поиска fulltext search в SQL Server

Введение

Full-text search - это дополнительный инструмент SQL Server для организации поиска по текстовым данным. LIKE оператор очень медленно работает и позволяет искать только по определенной маске без учета морфологии.

В этой статье рассмотрим как настроить и использовать Full-text search.

Установка Full-text search

Первым делом проверяем установлен или нет Full text search

SELECT

CASE FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

    WHEN 1 THEN 'Full-Text installed.'

    ELSE 'Full-Text is NOT installed.'

END;

https://stackoverflow.com/questions/6511167/full-text-search-installed-or-not

Также можно посмотреть в SQL Server Configuration Manager запущена или нет служба Fulltext search

Если пакет не установлен, то берем пакет установки SQL Server, выбираем доустановку компонентов, выбираем Full text и выполняем установку.

Создание каталога Full-text Search и индекса Full-text

В ManagementStudio/DB/Storage/Full-text catalog выполняем New Full-text catalog

Указываем имя, владельца (например, dbo)

После создания каталога переводим в его Свойства, открываем Таблицы и представления.

Выбираем таблицы и отмечаем галочками поля по которым мы планируем искать через Full-text search.

Создаем индекс.

Поля таблицы, которые участвуют в индексе должны быть BLOB(image, nvarchar(max)) и Allow null

Запросы select с Full-text search

Запрос без рейтинга (ranking score)

select id, title from kb_articles art

where  contains(art.title,'формами')

Запрос с рейтингом (в большинстве случаев имеет смысл использовать именно этот запрос):

select id, title, key_search.rank

from kb_articles art

INNER JOIN CONTAINSTABLE(kb_articles,(title),'форм') AS key_search

on art.Id = key_search.[Key]

-- or 
SELECT KEY_TBL.RANK,  id, title
FROM kb_articles art    
     INNER JOIN  
     FREETEXTTABLE(kb_articles, (title, text),  'работа с формами') AS KEY_TBL  
     ON KEY_TBL.[key] =art.id
ORDER BY KEY_TBL.RANK DESC

Поиск по нескольким значениям:

select id, title, key_search.rank

from kb_articles art

INNER JOIN CONTAINSTABLE(kb_articles,(title),'"форма" or "таблица"') AS key_search

on art.Id = key_search.[Key]

Использование FREETEXT в запросе (поиск по смыслу, а не формальному тексту, учет морфологии):

select id, title

from kb_articles art

where FREETEXT(title, N'еда форма календарь');

Поиск по нескольким полям:

select id, title

from kb_articles art

where FREETEXT((title,text), N'еда форма календарь');


select id, title

from kb_articles art

where FREETEXT(*, N'еда форма календарь');

Запрос похожих слов через Contains

SELECT ProductID, [Name]

FROM Production.Product

WHERE CONTAINS([Name], 'XL NEAR men NEAR shorts');

Примечание:

LIKE оператор работает гораздо медленнее, чем FTS
Используйте для переменных поиска тип nvarchar (N''). Если будет использоваться другой тип, это приведет к ненужным конвертациям, замедляющим запрос.

Пример SQL запроса, используемого для поиска по базе знаний SQL Server на сайте:

       insert into @result
       select top 30 id,
       		title name, 
            '',
            dbo.list_getProductURL(try_cast(id as nvarchar), code, 'sqlserver', '---') link,
            'sqlserver',
            '',
            KEY_TBL.RANK
        from kb_articles 
       	INNER JOIN  
     	FREETEXTTABLE(kb_articles, (title, text),  @search) AS KEY_TBL  
     	ON KEY_TBL.[key] =kb_articles.id
		where appCode='sql'  and  KEY_TBL.RANK > 100
       	ORDER BY KEY_TBL.RANK DESC