SQL Server Full-Text Search

FTS to „silnik” do szybkiego wyszukiwania danych tekstowych w bazie danych.
Działanie usługi FTS opiera się na budowaniu indeksów dla każdego znaczącego słowa lub wyrażenia zapisanego w bazie, co rozwiązuje problem wydajności wyszukiwania tekstu. Ponadto wykorzystując usługę FTS, mamy możliwość szukania:

• słów, które występują w tekście blisko siebie;
• z wykorzystaniem wieloznaczników (ang. wildcards);
• uwzględniającego formy fleksyjne wyrazów (np. search, searching, searched);
• z wykorzystaniem ważenia słów (jedno słowo jest ważniejsze w szukanym tekście niż inne);
• z wykorzystaniem tezaurusa.

W SQL Server 2005/2008 każda instancja serwera ma jedną dedykowaną instancję silnika Microsoft Full-Text Search (procesu MSFTESQL).


http://msdn.microsoft.com/en-us/library/ms142571.aspx

http://msdn.microsoft.com/en-us/library/cc280702%28v=sql.105%29.aspx
http://msdn.microsoft.com/en-us/library/ms174971%28v=sql.105%29.aspx
http://msdn.microsoft.com/en-us/library/ms174394%28v=sql.105%29.aspx

http://msdn.microsoft.com/en-us/library/ms142541%28v=sql.105%29.aspx



Sprawdzenie czy Full-Text Search jest zainstalowany: 

FULLTEXTSERVICEPROPERTY ('property')

http://msdn.microsoft.com/en-us/library/ms188418.aspx 

SELECT CASE WHEN FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1 

            THEN 'True' ELSE 'False' END as Install
 

http://www.techrepublic.com/blog/networking/adding-sql-full-text-search-to-an-existing-sql-server/5546


Sprawdzenie czy jest włączona obsługa funkcjonalności FTS:
W tym celu:

• z poziomu SQL Server Management Studio (SSMS) z menu kontekstowego bazy danych AdventureWorks wybieramy opcję Properties;
• przechodzimy na zakładkę Files;
• upewniamy się, że jest zaznaczona opcja Use full-text indexing.

Sprawdzenie z poziomu kodu T-SQL:

DATABASEPROPERTYEX ( database , property )

http://msdn.microsoft.com/en-us/library/ms186823%28v=sql.110%29.aspx

SELECT DATABASEPROPERTYEX(‚Database_Name’, ‚IsFulltextEnabled’) 

Zapytanie zwraca wartość 1, jeśli FTS jest włączone, w przeciwnym razie zwracana jest wartość 0. Do włączania i wyłączania obsługi FTS w bazie danych służy procedura sp_fulltext_database, która przyjmuje tylko dwa parametry: enable lub disable.

Full-Text Search Procedures
sp_fulltext_catalog	sp_help_fulltext_catalogs_cursor
sp_fulltext_column	sp_help_fulltext_columns
sp_fulltext_database	sp_help_fulltext_columns_cursor
sp_fulltext_service	sp_help_fulltext_tables
sp_fulltext_table	sp_help_fulltext_tables_cursor
sp_help_fulltext_catalogs

 Utworzenie katalogów FTS
W celu wykorzystania FTS musimy w pierwszym kroku utworzyć katalog FTS. Obiekt ten stanowić będzie zbiór indeksów typu FTS. Zasada przypisania/wykorzystania katalogów FTS w tabelach jest następująca: każdy katalog FTS może przechowywać wiele indeksów powiązanych z wieloma tabelami, ale każda tabela może być powiązana tylko z jednym katalogiem. Zazwyczaj jeden katalog będzie obsługiwał szukanie typu FTS dla całej bazy danych. Jednak ze względów wydajnościowych zaleca się dedykowanie oddzielnego katalogu dla dużych tabel.

Katalog FTS możemy utworzyć, wykorzystując do tego interfejs graficzny lub pisząc odpowiednie polecenie SQL.
 W pierwszej opcji w oknie SSMS przechodzimy do gałęzi Databases –> AdventureWorks –> Storage –> Full Text Catalogs, a następnie z menu kontekstowego wybieramy opcję New Full-Text Catalog.

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

Listę wszystkich utworzonych w bazie danych katalogów FTS otrzymamy, wykonując polecenie SELECT * FROM sys.fulltext_catalogs
http://msdn.microsoft.com/en-us/library/ms189520.aspx - CREATE
http://msdn.microsoft.com/en-us/library/ms176095.aspx - ALTER
http://msdn.microsoft.com/en-us/library/ms188403.aspx - DROP


Indeksy FTS
Kiedy mamy już utworzony katalog FTS, możemy przystąpić do tworzenia indeksów. Na początku jednak kilka uwag:

• każda tabela, dla której chcemy utworzyć indeks FTS, musi posiadać unikalny, niepusty indeks utworzony na pojedynczej kolumnie. W większości przypadków naszym wyborem będzie klucz główny tabeli;
• tylko jeden indeks FTS może być przypisany do pojedynczej tabeli (w przeciwieństwie do normalnych indeksów dostępnych w SQL Server 2005/2008) . Oznacza to, że każda kolumna, na podstawie której będzie wykonywane szukanie FTS, musi być częścią indeksu;
• indeksy FTS mogą obsługiwać tylko tabele użytkowników (nie są obsługiwane perspektywy, tabele tymczasowe i systemowe).

Ponownie do tworzenia indeksów FTS możemy użyć interfejsu graficznego lub odpowiedniego polecenia T-SQL.

CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) 
   KEY INDEX ui_ukJobCand 
   WITH STOPLIST = SYSTEM;

http://msdn.microsoft.com/en-us/library/ms187317.aspx - CREATE
http://msdn.microsoft.com/en-us/library/ms188359.aspx - ALTER
http://msdn.microsoft.com/en-us/library/ms184393.aspx - DROP


STOP-LISTY
W każdym języku występują wyrazy, które są bardzo często używane w mowie i piśmie, a które nie są znaczące dla całego przekazu. W języku angielskim są to np. he, it, a, the, on, for, itp. Wyrazy te określamy z ang. Noisy Words. Aby wyszukiwanie typu FTS było jak najbardziej efektywne, wyrazy te nie powinny być indeksowane.

W przypadku SQL Server 2005 w katalogu {SQL_SERVER_INSTALL_ DIRECTORY}\MSSQL\MSSQL\FTData przechowywane są pliki zawierające Noisy Words występujące w danym języku.

W przypadku SQL Server 2008 nazwa noisy została zmieniona na stop. Wyrazy zaś nie są już zapisywane w plikach na dysku, ale grupowane w tzw. stoplist i przechowywane w bazie danych.

Do tworzenia stoplist służy polecenie:

CREATE FULLTEXT STOPLIST myStoplist;

http://msdn.microsoft.com/en-us/library/cc280405.aspx - CREATE
http://msdn.microsoft.com/en-us/library/cc280871.aspx - ALTER
http://msdn.microsoft.com/en-us/library/cc280482.aspx - DROP


WYSZUKIWANIE INFORMACJI
Do szybkiego wyszukiwania danych tekstowych z wykorzystaniem usługi Full-Text Search służą cztery polecenia: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE.

Polecenie CONTAINS
http://msdn.microsoft.com/en-us/library/ms187787.aspx

Polecenie CONTAINS wykorzystujemy do wyszukiwania:

• słów lub wyrażeń;
• przedrostka słowa lub wyrażenia;
• słów, które występują w tekście stosunkowo blisko siebie;
• słów będących odmianami fleksyjnymi szukanego słowa;
• słów będących synonimami innych;
• z wykorzystaniem tezaurusa;
• z ważeniem szukanych słów (jedno szukane słowo jest ważniejsze w tekście niż inne).

 SELECT * FROM Production.Document WHERE CONTAINS (DocumentSummary, ‚”import*”‚ )


Polecenie FREETEXT
http://msdn.microsoft.com/en-us/library/ms176078.aspx

Polecenie FREETEXT jest bardzo podobne do polecenia CONTAINS. Jedyną różnicą jest metoda wyszukiwania. Polecenie FREETEXT, w przeciwieństwie do CONTAINS, nie skupia się na dokładnym wystąpieniu szukanych słów, ale na ich znaczeniu - różne formy słowa.


Polecenie CONTAINSTABLE
http://msdn.microsoft.com/en-us/library/ms189760.aspx

Polecenie CONTAINSTABLE w kwestii zwracanych wyników jest identyczne jak polecenie CONTAINS. Różnica między nimi polega na sposobie zwracania wyników.
Polecenie CONTAINSTABLE jako wynik zwraca tabelę składającą się z dwóch kolumn:

• KEY – wartość klucza głównego wiersza, który określaliśmy podczas tworzenia indeksu FTS. Dzięki tej wartości mamy możliwość odwołania się do konkretnego wiersza w tabeli;
• RANK – wartość z zakresu 0..1000, która wskazuje, jak trafnie dany wiersz odpowiada naszemu zapytaniu. Im większa wartość RANK, tym wynik jest bardziej trafny.

Polecenie FREETEXTTABLE
http://msdn.microsoft.com/en-us/library/ms177652.aspx

Analogia dotycząca poleceń FREETEXT i CONTAINS ma także odzwierciedlenie w przypadku poleceń FREETEXTABLE i CONTAINSTABLE.
Do wyszukiwania rekordów zawierających słowa bicycle, worn i replace (brane pod uwagę są ich fleksyjne odmiany oraz słownik tezaurus). Słowo bicycle ma największą wagę, zaś replace najmniejszą w przeszukiwanych tekstach. Zwrócone wyniki posortowane są wg trafności.





Wylistowanie Full-Text Search indeksów wraz z tabelą, kolumna i katalogiem:

SELECT

    t.name AS TableName,

    c.name AS FTCatalogName ,

    f.name AS FileGroupName,

    i.name AS IndexName,

    cl.name AS IndexColumnName,

    fi.is_enabled AS EnableIndex

FROM sys.tables t

       INNER JOIN sys.fulltext_indexes fi ON t.[object_id] = fi.[object_id]

       INNER JOIN sys.fulltext_index_columns ic ON ic.[object_id] = t.[object_id]

       INNER JOIN sys.columns cl ON ic.column_id = cl.column_id AND ic.[object_id] = cl.[object_id]

       INNER JOIN sys.fulltext_catalogs c ON fi.fulltext_catalog_id = c.fulltext_catalog_id

       INNER JOIN sys.filegroups f ON fi.data_space_id = f.data_space_id

       INNER JOIN sys.indexes i ON fi.unique_index_id = i.index_id AND fi.[object_id] = i.[object_id]

Check if all FTS mechnism existed:

SELECT catalogs.CatalogExisted, indexes.IndexExisted, indexcolumns.IndexColumnExisted

FROM

(

       SELECT CASE WHEN c.name is null THEN 'false' ELSE ' true' END as CatalogExisted

       FROM sys.fulltext_catalogs as c

              RIGHT JOIN ( SELECT 1 as name) as catalogs ON c.name = 'catalog_name'

) catalogs,

(

       SELECT CASE WHEN OBJECT_NAME(fti.object_id) is null THEN 'false' ELSE ' true' END as IndexExisted

       FROM sys.fulltext_indexes fti

              RIGHT JOIN ( SELECT 1 as name) as catalogs ON OBJECT_NAME(fti.object_id) = 'table_name'

) indexes,

(

       SELECT CASE WHEN t.name is null THEN 'false' ELSE ' true' END as IndexColumnExisted

       FROM sys.tables t

              INNER JOIN sys.fulltext_indexes fi ON t.[object_id] = fi.[object_id]

              INNER JOIN sys.fulltext_index_columns ic ON ic.[object_id] = t.[object_id]

              RIGHT JOIN ( SELECT 1 as name) as catalogs ON t.name = 'table_name'

) indexcolumns 

Remove all full-text indexes and catalogs :

-- drop indexes from catalogs

set @queryCursor = CURSOR FOR

select 'DROP FULLTEXT INDEX ON [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME(i.object_id) + ']'

from sys.fulltext_indexes i

INNER JOIN sys.tables t on i.object_id = t.object_id

open @queryCursor

while 1=1

begin

    fetch @queryCursor into @query

    if @@fetch_status != 0

              break

      

    exec(@query)

end

close @queryCursor

deallocate @queryCursor

-- drop catalog

set @queryCursor = CURSOR FOR

select 'DROP FULLTEXT CATALOG [' + name + ']'

from sys.fulltext_catalogs

open @queryCursor

while 1=1

begin

    fetch @queryCursor into @query

    if @@fetch_status != 0

              break

      

    exec(@query)

end

close @queryCursor

deallocate @queryCursor

Materiały polskie:
http://software.com.pl/full-text-search-wyszukiwanie-pelnotekstowe-w-sql-server-20052008/
http://www.dev.cdur.pl/Artykuly/Artykul/24

Pozostałe:
http://msdn.microsoft.com/en-us/library/ms142571.aspx
http://technet.microsoft.com/pl-pl/library/ms142571%28v=sql.110%29.aspx
http://www.sql-server-performance.com/2010/full-text-search-2008/
http://www.codeproject.com/Articles/29237/SQL-SERVER-2008-Creating-Full-Text-Catalog-and-Ful
http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
http://msdn.microsoft.com/en-us/library/cc280702.aspx