Why Full Text-Index?

Search for character/string type data on a large database using LIKE operator, especially pattern matching starting with % requires full table scan. 
It is a complex and huge time taking task. Also requires lot of memory and CPU time. To overcome this problem, we use a special kind of index called full-text index.
Full text indexes are populated on large data using B-Trees. 

When search is done, either first half or the second half of tree is searched based on character used to search (Say searching for character 'X', second half of tree is visited). Search is narrowed by half the values each time till the element is found.
When data is added in column where full-text index is created, index needs to be updated until which the updated data is not shown in search results.
Updating (called population) can be automatic which slows down performance. 

When immediate search results after updating data are required, this option is used (For columns like uuResumeText of Consultants table in cBiz)
To manually repopulate index, job schedule is created to run on a particular time


--Syntax for creating Full Text Catalog
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}

--Syntax for creating Full Text Index
CREATE FULLTEXT INDEX ON table_name
	[(column_name [TYPE COLUMN type_column_name]
	[LANGUAGE language_term] [,...n])]
KEY INDEX index_name
	[ON fulltext_catalog_name]
	[WITH 
	{CHANGE_TRACKING {MANUAL | AUTO | OFF [, NO POPULATION]}}
	]

--Syntax to Rebuild or Reorganize Full Text Catalog
ALTER FULLTEXT CATALOG catalog_name 
{ REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ]
| REORGANIZE
| AS DEFAULT 
}


CONTAINS() & FREETEXT() are functions generally used for searching with full text index