Настройка полнотекстового поиска 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''). Если будет использоваться другой тип, это приведет к ненужным конвертациям, замедляющим запрос. 

Материалы по теме

http://sqlcontent.com/index.php/2018/04/04/installing-full-text-search-with-sql-2017/

https://itnext.io/full-text-search-how-to-install-configure-and-use-it-with-sql-server-and-outsystems-23fcf316e870

https://codingsight.com/implementing-full-text-search-in-sql-server-2016-for-beginners/

https://www.sswug.org/alexanderchigrik/sql-server/some-tips-for-using-full-text-search-in-sql-server-2016/

https://www.sqlshack.com/hands-full-text-search-sql-server/

https://www.mssqltips.com/sqlservertip/1382/expand-search-capabilities-with-the-sql-server-freetext-command

https://www.mssqltips.com/sqlservertip/1342/full-text-search-querying-alternatives-in-sql-server/

https://codingcanvas.com/full-text-queries-containscontainstablefree-text-and-freetexttable/

https://codingcanvas.com/full-text-search-basics-and-configuration/

https://docs.microsoft.com/ru-ru/sql/relational-databases/search/query-with-full-text-search?view=sql-server-ver15

Примечание