Apr 22 2010

Full-Text Search in SQL Server 2008

Categorie: Index | OLTP | SQL Server | T-SQL | TutorialCatalin Dumitru @ 07:36

Functionalitatea Full Text Search poate fi utilizata de dezvoltatori pentru a rula interogari peste seturi de date de tip sir de caractere stocate intr-o tabela. Pentru a utliza full text search trebuie creat un full text index pentru tabela care stocheaza sirurile de caractere. Pentru o tabela sau view indexat se poate crea un singur full text index. Acest index poate fi creat pentru coloane de tipul: char, nchar, varchar, nvarchar, text, ntext, varbinary, varbinary(max), image si xml.
Fiecare index full text poate fi utilizat pentru a indexa una sau mai multe coloane din tabela sau view-ul indexat peste care este definit, si fiecare coloana poate avea un limbaj suportat de full text search. In SQL server 2008 sunt disponibile peste 50 de limbaje precum English, French, German etc. Lista cu limbajele suporate se poate obtine ruland instructiunea urmatoare:

SELECT * FROM sys.fulltext_languages ORDER BY name ASC 

In cazul in care functionalitatea nu a fost instalata, se poate realiza acest lucru ruland SQL Server Installation Center iar din sectiunea Installation se alege optiunea “New SQL Server stand-alone installation or add feature to an existing installation”, asa cum se poate observa in imaginea de mai jos.

Odata ce functionalitatea Full-Text Search a fost instalata in Configuration Manager o sa apara un nou serviciu si anume “SQL Full-text Filter Daemon Launcher”, acesta trebuie sa fie pornit, in caz contrar interogarile nu vor rula si vor genera erori.

Configurarea bazei de date pentru cautari full text search

Pentru a folosi full text search pe o tabela sau view indexat necesita cativa pas ice vor fi prezentati in continuare:  

  1. Crearea unui catalog full text search
  2. Crearea unui index full text search
  3. Popularea indexului full text search

Crearea unui catalog full text search

 In object explorer, se expandeaza Databases si apoi baza de date in care se va crea catalogul. Mai departe, se expandeaza Storage iar apoi se executa click cu butonul din dreapta al mouse-ului deasupra optiunii “Full Text Catalogs” din meniul deschis si se selecteaza optiunea “New Full-Text Catalog…”, asa cum este prezentat in imaginea de mai jos. 

In fereastra nou deschisa trebuie precizat un nume pentru noul catalog (ex: AdventureWorksFullTextCatalog) si un owner (ex: dbo). 

Dupa salvare, in object explorer, in lista de cataloage full text va apare si catalogul proaspat creat. 

Crearea indexului full text

Odata ce a fost creat catalogul full text, urmatorul pas este crearea indexului. In exemplul urmator se va crea un index pe tabela HumanResources.Employee din baza de date AdventureWorks. La inceputul articolului spuneam ca pe o tabela se poate crea un singur index full text.

Se da click cu butonul din dreapta al mouse-ului pe tabela in cauza si se selecteaza optiunea “Full-Text Index” si apoi “Define Full-Text Index” din meniul deschis. 

In fereastra “Welcome to the SQL Server Full-Text Indexing Wizard” se apasa Next pentru a trece la fereastra urmatoare.

 

In fereastra “Select an Index” va fi afisata o lista cu indecsi definita pe tabela HumanResources.Employee in controlul drop down. Aici se selecteaza un index unic (ex: PK_Employee_EmployeeID) si seapasa Next. 

In fereastra “Select Table Columns” va fi afisata o lista cu coloanele disponibile pentru indexare, va trebui select limbajul fiecarei coloane ce va face parte din index.

 

In fereastra “Select Change Tracking” se selecteaza o metoda de urmarie a modificarilor. Cand se selecteaza Automatic sau Manual, popularea indexului incepe imediat ce wizard-ul ajunge la final. Pentru a evita acest lucru se selecteaza “Do not track changes” si se debifeaza optiunea “Start full population when index is created”. 

In fereastra “Select Catalog, Index File Group, and Stoplist” se selecteaza catalogul full text creat mai devreme (se poate crea si aici un catalog nou), si filegroup-ul unde va fi stocat indexul.

 

In fereastra “Define Population Schedules (Optional)” se poate seta un calendar dupa care se populeaza indexul. Pentru populare manuala, se apasa Next.

 

In fereastra “Full-text Index Wizard Description” se poate vedea un scurt rezumat al tuturor setarilor de pana la acest moment. Prin apasarea butonului Finish wizard-ul a ajuns la final.

Popularea unui index Full-text

Inainte de a incepe utilizarea interogrilor pe Full-text trebuie populat indexul, acest lucru se poate reliza dand click pe butonul din dreapta al mouse-ului pe tabela parinte a indexului, din meniul deschis se selecteaza “Full-text Index” si apoi “Start Full Population”

 

De indata ce indexul a fost creat si populat, se pot rula interogari full-text. SQL Server 2008 ofera predicatele CONTAINS si FREETEXT dar si functiile de tip tabela CONTAINSTABLE si FREETEXTTABLES.

Predicatul FREETEXT

Predicatul FREETEXT se foloseste in clauza where pentru cautari pe coloane de tip sir de caractere a textelor sau variatiunilor acestora (cautarea se poate realize si dupa forma textului, nu neaparat cautare exacta). Cand se foloseste predicatul FREETEXT, SQL server 2008 creaza relevant pentru fiecare cuvant si pe baza acestei relevante sunt intoarse rezultatele.

Predicatul CONTAINS

Predicatul CONTAINS se foloseste in clauza where pentru cautari in campurile de tip sir de caractere a unui cuvant sau fraza. Pentru a combina mai multe cuvinte se pot folosi opertori precum AND sau OR in textul cautat. Cautarea se poate realiza cautand txtul exact sau mai putin precis (adica variatiuni).
Precizia predicatului CONTAINS este mai mare decat in cazul predicatului FREETEXT

Exemplu de utilizare a functionalitatii Full-Text Search

USE AdventureWorks

GO

SELECT EmployeeID, Title

FROM HumanResources.Employee

WHERE FREETEXT(*, 'Recruiter')

GO

SELECT TOP 5 EmployeeID, Title

FROM HumanResources.Employee

WHERE CONTAINS ([Title], 'Recruiter OR Manager')

 

Concluzie

In acest articol a fost prezentata configurarea si utilizarea functionalitatii Full-Text Search. Dezvoltatorii pot utilize aceasta functionalitate pentru a rula interogari complexe peste date de tip sir de caractere. Acest tutorial este mai degraba un ghid introductive, pentru a beneficia de toata puterea acestei functionalitati este necesara studierea capitolului Full Text Search din books online.

Etichete: , , , , ,

Comentarii

1.
Alex spune:

Salut!

Daca am Contains(*, @parametru) si parametrul este NULL, ce ar trebui sa fac?
Am incercat cu Contains(*, ISNULL(@parametru, @parametrul2)), dar ISNULL nu este permis.
Am incercat si cu WHERE @parametru IS NOT NULL AND CONTAINS(*, @parametru), dar tot primesc eroarea.

Multumesc!

2.
admin spune:

Salut,

Solutia este sa scoti ISNULL() in afara interogarii si sa inlocuiesti NULL cu "" (ghilimele ghilimele), adica sa prelucrezi variabila @parametru inainte sa o folosesti ca input pt contains, ca in exemplul de mai jos:

declare    @SirCautat nvarchar(1000) = NULL --'test'
set    @SirCautat = ISNULL(@SirCautat, '""')

--daca @SirCautat nu este specificat se returneaza toate inregistrarile
select    *
from    TabelaTest
where    contains(ColoanaTest, @SirCautat) OR @SirCautat = '""'

--daca @SirCautat nu este specificat nu se returneaza nimic
select    *
from    TabelaTest
where    contains(ColoanaTest, @SirCautat) --OR @SirCautat = '""'

3.
Alex spune:

Salut,

acum nu mai mult de 10 minute am rezolvat si eu problema
cererea arata asa acum: WHERE @SirCautat IS NOT NULL AND CONTAINS(Coloana, @SirCautat) OR 1 = 1

Multumesc!


4.
admin spune:

1=1 este mereu adevarata si indiferent de ce valoare ai in @SirCautat ar trebui sa ti se intoarca toate randurile din tabela.

5.
Alex spune:

Greseala mea.

declare    @SirCautat nvarchar(1000) = NULL --'test'
set    @SirCautat = ISNULL(@SirCautat, '""')

--daca @SirCautat nu este specificat se returneaza toate inregistrarile
select    *
from    TabelaTest
where    contains(ColoanaTest, @SirCautat) OR @SirCautat = '""'

Este solutia