Wyszukiwanie pełnotekstowe pozwala na przybliżone wyszukiwanie w bazach SQL Serwera. Pierwszym krokiem do wyszukiwania pełnotekstowego jest utworzenie indeksów pełnotekstowych w katalogach pełnotekstowych. Kiedy utworzymy pełnotekstowe indeksy na kolumnach tekstowych w naszej bazie danych, możemy wyszukiwać dane za pomocą:

• Prostych termów (simple terms) - jeden lub więcej wyraz lub fraza.
• Prefiksów - termy od których zaczyna się wyraz lub fraza.
• Termy fleksyjne - fleksyjne formy wyrazów.
• Termy przybliżone, wyrazy lub frazy podobne do innych wyrazów lub fraz.
• Synonimy.
• Wyszukiwanie statystyczne lub po kluczach (semantic search).
• podobne dokumenty, gdzie podobieństwo jest definiowane za pomocą semantycznych fraz kluczowych.

Komponenty wyszukiwania pełnotekstowego

Aby rozpocząć pracę z wyszukiwaniem pełnotekstowym należy sprawdzić czy mechanizm FULL-TEXT SEARCH jest zainstalowany na serwerze za pomocą zapytania T-SQL:

SELECT SERVERPROPERTY('IsFullTextInstalled');

Indeksy pełnotekstowe można tworzyć na kolumnach typów:

• CHAR
• VARCHAR
• NCHAR
• NVARCHAR
• TEXT
• NTEXT
• IMAGE
• XML
• VARBINARY(MAX)

Poza użyciem indeksów pełnotekstowych na kolumnach znakowych, mozna przechowywać cały dokument jako kolumny binarne lub XML i używać wyszukiwania pełnotekstowego do tych dokumentów. Kolumny typór VARBINARY(MAX), IMAGE lub XML wymagają zadeklarowania rozszerzenia pliku jaki przechowują (np .docx, .pdf, .xslx) w każdym wierszu. Do obsługi dokumentów potrzebne są filtry dokumentów. Filtry te zwane IFILTERS w terminologii wyszukiwania pełnotekstowego, wyodrębniają tekstową informację i usuwają formatowanie dokumentów. Sprawdzenie jakie filtry są zainstalowane odbywa się z użyciem zapytania:

EXEC sys.sp_help_fulltext_system_components 'filter';

Lub zapytania:

SELECT document_type, path
FROM sys.fulltext_document_types;

Wiele popularnych formatów jest wspieranych domyślnie. Można instalować dodatkowe filtry, jak np filtr dla dokumentów MS Office 2010. Instaluje je się wg instrukcji dołączonej do paczki z filtrami. Po zainstalowaniu trzeba taki filtr zarejestrować w instancji SQL Serwera używając komendy:

EXEC sys.sp_fulltext_service 'load_os_resources', 1;

Ponieważ leksykalne reguły różnią się dla różnych języków to trzeba sprawdzić jakie języki są wspierane przez mechanizmy SQL Serwera używając zapytania:

SELECT lcid, name
FROM sys.fulltext_languages
ORDER BY name;

Charakterystycznymi różnicami dla języków są podziały wyrazów oraz znaki oddzielające wyrazy. Domyślnym językiem wyszukiwania pełnotekstowego jest język instancji o ile jest wspierany przez mechanizmy FULL-TEXT. Jeżeli język nie jest wspierany lub instancja serwera jest bez ustawień lokalizacji to domyślnym językiem jest język angielski. Dla każdej kolumny wyszukiwania pełnotekstowego można zdefiniować inny język  Definicja języka jest zmieniana z użyciem procedury systemowej SYS.SP_CONFIGURE. * Załóżmy, że mamy dokument o serwerze SQL. Faza "SQL Server" prawdopodobnie powtarza się w nim wielokrotnie. Takie frazy nie pomagają w wyszukiwaniu jednak zwiększa indeks FULL-TEXT. Można zapobiegać indeksowaniu takich zwrotów za pomocą definiowania stoplist lub stopword. Aktualne wpisy stopwords i stoplists można sprawdzić zapytaniem:

SELECT stoplist_id, names
FROM sys.fulltext_stoplists;
SELECT stoplist_id, stopword, language
FROM sys.fulltext_stopwords;

Indeksy FULL-TEXT potrafią także wyszukiwać synonimy. Synonimy w SQL Serwerze znajdują się w plikach thesaurus. Lokalizacja plików thesaurus dla instancji domyślnej to : SQL_Server_install_path\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\FTDATA\. Każdy plik można edytować i manualnie konfigurować następujące elementy:

• diacritics_sensitive - wartość 1 jeżeli element w danym języku jest akcentowany lub 0 jeżeli nie (AS/AI).
• expansion - słowo rozszerzające dane słowo np słowo "autor" może być rozszerzeniem słowa "pisarz".
• replacement - definiuje zamiennik dla wyrażenia np "Windows 2008" może być zamieniane z wyrażeniem "Win 2k8.". W takim wypadku dla wyszukiwanego słowa "Win 2k8." SQL Serwer znajdzie również wystąpienia wyrażenia "Windows 2008".

Po edycji plików thesaurus dla danego języka,  muszą one być załadowane do serwera poprzez wywołanie procedury z numerem języka jako parametrem (1033 -US English): EXEC sys.sp_fulltext_load_thesaurus_file 1033;

Tworzenie i zarządzanie indeksami i katalogami pełnotekstowymi

Indeksy pełnotekstowe są przechowywane w katalogach pełnotekstowych. katalog pełnotekstowy jest wirtualnym obiektem, kontenerem na indeksy pełnotekstowe. Jako obiekty wirtualne nie należą one do żadnej grupy plików. Katalog wyszukiwania pełnotekstowego tworzy się wg poniższej składni:

CREATE FULLTEXT CATALOG catalog_name
[ON FILEGROUP filegroup ]
[IN PATH 'rootpath']
[WITH <catalog_option>]
[AS DEFAULT]
[AUTHORIZATION owner_name ] <catalog_option>::=
ACCENT_SENSITIVITY = {ON|OFF}

Pozycje ON FILEGROUP i IN PATH są opcjami wstecznej kompatybilności i nie powinny być deklarowane w SQL Serwerze 2012. Opcja ACCENT_SENSITIVITY określa czy indeksy pełnotekstowe w katalogu uwzględniają akcentowanie. Jeżeli ta opcja zostaje zmieniona to wszystkie indeksy w katalogu muszą zostać przebudowane. Edycji katalogu wyszukiwania pełnotekstowego dokonuje się z użyciem polecenia ALTER FULLTEXT CATALOG, usunięcia natomiast za pomocą polecenia DROP FULLTEXT CATALOG. Po utworzeniu katalogu można utworzyć indeksy pełnotekstowe. Składnia tworzenia indeksu pełnotekstowego jest następująca:

CREATE FULLTEXT INDEX ON table_name
[ ( { column_name
[ TYPE COLUMN type_column_name ]
[ LANGUAGE language_term ]
[ STATISTICAL_SEMANTICS ]
} [ ,...n]
) ]
KEY INDEX index_name
[ ON <catalog_filegroup_option> ]
[ WITH [ ( ] <with_option> [ ,...n] [ ) ] ] [;] <catalog_filegroup_option>::=
{
fulltext_catalog_name
| ( fulltext_catalog_name, FILEGROUP filegroup_name )
| ( FILEGROUP filegroup_name, fulltext_catalog_name )
| ( FILEGROUP filegroup_name ) } <with_option>::=
{
CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }
| STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
| SEARCH PROPERTY LIST [ = ] property_list_name
}

Większość opcji jest łatwa do zrozumienia po nazwie. Najważniejsze z nich to:

• KEY INDEX index_name - jest to nazwa unikalnego klucza indeksu na tabeli. Trzeba użyć kolumny unikalnej, jedno-kluczowej która nie może mieć wartości NULL. 
• CHANGE_TRACKING [=]{MANUAL|AUTO|OFF[,NO POPULATION]} - ta opcja określa czy SQL Serwer aktualizuje indeks pełnotekstowy automatycznie. SQL Serwer używa mechanizmu CHANGE TRACKING do śledzenia zmian.
• STATISTICAL_SEMANTIC - ta opcja tworzy dodatkową frazę kluczową i dokument indeksów podobieństwa, które są częścią statystycznego semantycznego indeksowania.

 Ćwiczenia

I. Utworzenie tabeli testowej w bazie danych TSQL2012, wypełnienie jej danymi tekstowymi i utworzenie list STOPWORDS i STOPLIST.

1. Utworzenie tabeli DBO.DOCUMENTS o następującej struktórze:

CREATE TABLE dbo.Documents
(
id INT IDENTITY(1,1) NOT NULL,
title NVARCHAR(100) NOT NULL,
doctype NCHAR(4) NOT NULL,
docexcerpt NVARCHAR(1000) NOT NULL,
doccontent VARBINARY(MAX) NOT NULL,
CONSTRAINT PK_Documents
PRIMARY KEY CLUSTERED(id)
);

2. Wstawienie 4 rekordów do tabeli DBO.DOCUMENTS

INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT
N'Columnstore Indices and Batch Processing',
N'docx',
N'You should use a columnstore index on your fact tables, putting all columns of a fact table in a columnstore index. In addition to fact tables, very large dimensions could benefit from columnstore indices as well. Do not use columnstore indices for small dimensions. ',
bulkcolumn FROM OPENROWSET(BULK 'D:\L13\ColumnstoreIndicesAndBatchProcessing.docx',SINGLE_BLOB) AS doc;

INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT
N'Introduction to Data Mining',
N'docx',
N'Using Data Mining is becoming more a necessity for every company and not an advantage of some rare companies anymore. ',
bulkcolumn FROM OPENROWSET(BULK 'D:\L13\IntroductionToDataMining.docx', SINGLE_BLOB) AS doc;

INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT
N'Why Is Bleeding Edge a Different Conference',
N'docx',
N'During high level presentations attendees encounter many questions. For the third year, we are continuing with the breakfast Q&A session. It is very popular, and for two years now, we could not accommodate enough time for all questions and discussions! ',
bulkcolumn FROM OPENROWSET(BULK 'D:\L13\WhyIsBleedingEdgeADifferentConference.docx', SINGLE_BLOB) AS doc;

INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT
N'Additivity of Measures',
N'docx',
N'Additivity of measures is not exactly a data warehouse design problem. However, you have to realize which aggregate functions you will use in reports for which measure, and which aggregate functions you will use when aggregating over which dimension.',
bulkcolumn FROM OPENROWSET(BULK 'D:\L13\AdditivityOfMeasures.docx', SINGLE_BLOB) AS doc;

3. Dodanie listy właściwości wyszukiwania o nazwie WordSearchPropertyList. Sosanie właściwości "Authors" do listy. Właściwości dokumentów mają zdefiniowane identyfikatory GUID id całkowice (integer) ID (http://msdn.microsoft.com/en-us/library/ee677618.aspx). Właściwość opisująca autorów dla dokumentów Office posiada GUID = 29F85E0-4FF9-1068-AB91-08002B27B3D9 i wartość ID = 4. 

CREATE SEARCH PROPERTY LIST WordSearchPropertyList;
GO
ALTER SEARCH PROPERTY LIST WordSearchPropertyList
ADD 'Authors'
WITH (
PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9',
PROPERTY_INT_ID = 4,
PROPERTY_DESCRIPTION = 'System.Authors - authors of a given item.'
);

4. Dodanie listy STOPWORDS o nazwie SQLStopList i dodanie do niej słowa SQL używając języka angielskiego.

CREATE FULLTEXT STOPLIST SQLStopList;
GO
ALTER FULLTEXT STOPLIST SQLStopList
ADD 'SQL' LANGUAGE 'English';

5. Sprawdzenie czy lista została dodana.

SELECT
w.stoplist_id,
l.name,
w.stopword,
w.language
FROM sys.fulltext_stopwords AS w
INNER JOIN sys.fulltext_stoplists AS l
ON w.stoplist_id = l.stoplist_id;

6. Użycie widoku dynamicznego sys.dm_fts_parser do sprawdzenie jak wyszukiwanie pełnotekstowe analizuje wpisy dodane do listy STOPLIST w wybranym języku. Funkcja ta przyjmuje jako parametry kolejno:

• ciąg znaków do analizy.
• kod języka (1033 dla US English).
• stoplisdid (z poprzedniego zapytania).
• flaga oznaczająca czy analiza ma być czuła na akcenty.

SELECT *
FROM sys.dm_fts_parser
(N'"Additivity of measures is not exactly a data warehouse design problem. However, you have to realize which aggregate functions you will use in reports for which measure, and which aggregate functions you will use when aggregating over which dimension."', 1033, 5, 0);

II. Instalacja Semantic Database i utworzenie indeksu pełnotekstowego.

1. Sprawdzenie czy baza Semantic Language Statistics Database jest zainstalowana odbywa się z użyciem zapyatania T-SQL:

SELECT *
FROM sys.fulltext_semantic_language_statistics_database;

Jeżeli baza nie jest zainstalowana należy uruchomić SemanticLanguageDatabase.msi z folderu SETUP i zainstalować do folderu z uprawnieniami zapisu i odczytu w którym znajdują się bazy danych po czym należy dołączyć ją do instancji serwera za pomocą zapytania:

CREATE DATABASE semanticsdb ON
(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb.mdf'),
(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb_log.ldf')
FOR ATTACH;

Po dołączeniu bazy danych trzeba ją zarejestrować w instancji jako bazę semantyczną za pomocą polecenia:

EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb';

2. Utworzenie katalogu wyszukiwania pełnotekstowego o nazwie DocumentsFtCatalog za pomocą polecenia T-SQL:

CREATE FULLTEXT CATALOG DocumentsFtCatalog;

3. Utworzenie indeksu pełnotekstowego na kolumnach docexcerpt i doccontent z użyciem automatycznego śledzenia zdarzeń za pomocą polecenia:

CREATE FULLTEXT INDEX ON dbo.Documents
(
docexcerpt Language 1033,
doccontent TYPE COLUMN doctype Language 1033 STATISTICAL_SEMANTICS
)
KEY INDEX PK_Documents
ON DocumentsFtCatalog
WITH STOPLIST = SQLStopList,
SEARCH PROPERTY LIST = WordSearchPropertyList,
CHANGE_TRACKING AUTO;

 Podsumowanie

1.  SQL Server umożliwia tworzenie katalogów i indeksów pełnotekstowych.
2. Do wyszukiwania pełnotekstowego można dodać listy STOPLISTS i STOPWORDS oraz dodać możliwość wyszukiwania po właściwościach dokumentu.
3. Można użyć widoku systemowego  sys.dm_fts_parser do sprawdzania jak mechanizmy pełnotekstowe przeszukują wybrany dokument.