Full Text Search Internals - part 3

Overview :

Full text search provides the ability to search the character based data
1) character data
2) varbinary data -->  when html documents are used for full text search  ms-locale meta tag is used to define the language for the full text search
3) xml data  --> xmllang is used to define the language for search
4) file stream data

Full text search functionality includes
1) simple search  using freetext
2) prefix search using contain
3)inflectional forms of same words( like run, ran etc ) using contains & form of inflectional
4) words near other words using contains & nearby
5) ranking values & weighted values using containstable & isabout

Components:

1) supports close to 50 languages .
   SELECT        lcid, name  FROM            sys.fulltext_languages
2) for each language there would be a word breaker & stemmer.
   word breaker breaks the document sentences in to word & decide on what constitues the words where as stemmers look for the inflectional forms of words. Third party word breakers can be purchased & supported,
stemmers are not invoked when full text search index is populated , they are invoked when full text search is done.
3) per instance thesauraus file.  they are the xml files on the hard disk located @
   C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FTData

they are used for expansion sets & replacement sets ( if the search spells wrong then thesarus looks for the right words from xmls, replaces them & returns the search results on right words.
4) stop lists called as noice words, they are moved to internal tables in sql server 2008 . we can add the stop list per full text index.

Ifilters :

Filters are provided for each document type one for .doc, .docx, .pdf etc. Full text search looks for the registry and loads the ifilters for newly added ifilters. Filters implement Ifilter interface , third party filters can be supported.
Ifilters are used to parse the documents during the creation of fulltext index population not during the querying of data. We can provide the type of document for each row in the table & based on the type relevant ifilter is loaded , so same table can support multiple document types .

New Ifilters (or) third party Ifilters can be loaded using command :

exec sp_fulltext_service 'load_os_resources' ,1

Service :

Sql server 2005 used the external windows service for word breaker & stemmer. In  sql server 2008 those service are brought in to sql server. FDHost launch service should be up & running to support the full text search.  search runs with in the sql server engine.

Programming :

Full text search provides 4 programming functions,
1) Freetext 2) Contains  are predicate based
3) Freetexttable 4) containstable are table valued functions.

1) Free text is to search the scentence , it does the word breaking, stemmer (inflectional words ) , thesaurus ,stop words & finds documents.
2) contains - all of the above are not done automatically, need to explicitly define thesarus, forms of inflectional etc  based on what we are looking for.

---------

We can find the words that are indexed in the full text search per table with command :
select * from sys.dm_fts_index_keywords( DB_ID(), OBJECT_ID('HumanResources.Employee'))

fts parser gives the exact matches , stop word & numbers for a word given in the search.
Numbers will have nn in it.

sys.internal_tables  gives more information on full text .

DOCID is the  unique id which refers to the rows in the table. when the table has unique int primary key ,then that key will act as the DOC ID.

Thesaurus files can be modified &  loaded  as shown in diagram,

SQL Server Denali Improvements :

1) Indexing is made multi threading, in 2008 it is single threaded.
2) predicative performance is improved. ex: search for many things like contains( tree) and contains (road ) , they modified to convert ( tree and road )
3) faster time response using streamed table valued functions, means we don't have to wait till all rows are returned. they stream the rows.
4)More granular locks & min time locking.
5) property based search for the documents, like  document author name, date created etc..
 properties are per index & when ever properties are updated , full text index must be repopulated.
Ifilters must support the extraction of properties .

-----------

Full Text Search Basics -part 2

Here are the steps to create full text index from UI.

Full Text Search Basics

Sql server full text search is a feature used to scan or search the large document stored in a table like documents, html, varbinary etc..we can do a like '%'  search, but these are much more efficient & optimised .
It can also rank the results based on the proximity of words you define & the parts of content you search for, inflectional words . We can tweak the index performance , avoid stop list words from search etc..

By default databases are enabled for full text search unless databases are created using ssms & we can enable the full search text by right clicking database s -> select properties -> check full text indexing

To create full text index --> catalog has to be created to store one or more full text indexes & full text indexes have to be created on them.

Adventureworks database is used for demo.

1) Creating Catalog :
Create FullText Catalog MyCatalog
This creates a folder in the specified directory during installation to store the full text indexes on the hard drive.
Catalog stores alll index files & configuration files. full text indexes store each word from each document indexed and maps them back to the table row for reference.

2) There should be primary key on the table which is unique & non nullable . create primary key if it doesn't exist.

3) create full text index on the table

create fulltext index on production.productreview (comments)
key index PK_ProductReview_ProductReviewID on MyCatalog
with change_tracking auto

comments is the column on which we want to full text index.

PK_ProductReview_ProductReviewID  is the unique primary key on the table

MyCatalog is the catalog on which we want the full text index to be on
change_tracking -> created full text index needs to be populated with data & it has different population modes.
1) Full Population is done for the first time after full text index is created
2) Incremental population can be done either based on change tracking or incremental time stamp based.
 
Full text index is very IO oriented  & intensive operation. so it must scheduled & maintain during down time.


Creating one more advanced full text index on table  production.document.

create fulltext index on production.document
(
document
type column FileExtension
language 0x0
)
key index PK_Document_DocumentID on MyCatalog
with change_tracking auto

Above query says,  create full text index on column document in table production.document & the document columns holds data of type specified in Fileextension column . File extension is used to load the appropriate filter .  lanaguage  0x0 is neutral language but we can specify any language needs to be used for parsing the text . so that it can use the appropriate language dictionary to parse the text from document.

 sys.fulltext_document_types  gives all the list of document types full text index can support.

FreeText() is a predicate , query engine splits the sentence in to words (word breaking )
generates inflectional forms of the words ( stemming)
identifies a list of matching words based on the words from thesaurus.

Now querying :
Basic query :

We can use different search conditions for contains :
1) like wild cards  where contains(  comments, '"mount*"')  matches to mountain, mount , mounting etc.

2) can use binary operations like AND, OR, NOT.
   ex : contains ( comments, ' "mountain"  AND   "flag"'  NOT   "flagging"  ')

3) can use near operations which lists all the documents which have both the words & ranks higher the one which are more closer to each other.

4) we can use isabout   to give  the different weights to different words & they will be ranked on the weight given. the words with more weight show up first & then the rest follows in the order of weights given.

the above query contains the containstable  which creates a temporary table  taking  the bay as wild card with different weights given to different words. since street has more weight than view ,  bay wild card with street shows up first ,then comes the rest . we joined the temporary table with the actual table on address id (pk) & then order by rank desc,

5) we can use the inflectional words which give similar meaning  like foot returning documents with foot, feet etc.

Any thing related to log for Full Text Index can be found   @
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

with file name starting with SQLFT...

Full text catalogs in a database can be found using the system table sys.fulltext_catalogs

The list of lanaguages that full text can support can be found using sys.fulltext_languages
the lcid can be declared when we create a full text index to search on the language  desired.

Stop list is the words list which will not be used by full text search to search on. they are most generally used words in day to day life. by using stop list we can narrow down the words to search for.
we can add a stop list to full text index by :


CREATE FULLTEXT STOPLIST stList
FROM SYSTEM STOPLIST;

we can query the stop list  words as below :

to  add or remove words from stop list :


ALTER FULLTEXT STOPLIST stList
ADD / DROP 'pen' LANGUAGE 1033;

language code 1033 is for english ( which is nothing but lcid discussed above )

Misc : Only one free text index is allowed per table & it can have 1024 columns