Fulltext Index Search

Create Fulltext Catalog

Syntax
CREATE FULLTEXT CATALOG catalog_name

Creates a full-text catalog for a database. One full-text catalog can have several full-text indexes, but a full-text index can only be part of one full-text catalog. Each database can contain zero or more full-text catalogs

Create Fulltext Catalog

Example
CREATE FULLTEXT CATALOG FTCcse;
SELECT * FROM sys.fulltext_catalogs;

Drop Fulltext Catalog

Syntax
DROP FULLTEXT CATALOG catalog_name
Example
DROP FULLTEXT CATALOG FTCcse

Create Fulltext Index

Syntax
CREATE FULLTEXT INDEX ON table_name
	[{column_name [LANGUAGE language_term]}]
	KEY INDEX index_name
	ON catalogName
	[WITH STOPLIST = {OFF | SYSTEM | stoplist_name}]

Create Fulltext Index

Syntax
CREATE FULLTEXT INDEX ON Product (ProductName)
	KEY INDEX PK_Product_ProductId ON FTCcse;
SELECT * FROM sys.fulltext_indexes;

Create Fulltext Stoplist

Syntax
CREATE FULLTEXT STOPLIST stoplist_name
	[FROM SYSTEM STOPLIST]

Create Fulltext Stoplist

Example
CREATE FULLTEXT STOPLIST FTSLcse;
SELECT * FROM sys.fulltext_stoplists;

Create Fulltext Stoplist

Add stopwords
ALTER FULLTEXT STOPLIST FTSLcse ADD 'stopword' LANGUAGE 'Vietnamese';
SELECT * FROM sys.fulltext_stopwords WHERE language_id = 1066;

Create Fulltext Index With Stoplist

Example
CREATE FULLTEXT INDEX ON Product(ProductName LANGUAGE 1066) 
	KEY INDEX PK_Product_ProductId WITH STOPLIST = FTSLcse;

Contains

Syntax
CONTAINS({column_name | (column_list) | *}, 
	'contains_search_condition')
Example
SELECT * FROM Product WHERE CONTAINTS(ProductName, 'Nokia');
Or
SELECT * FROM Product WHERE CONTAINTS(ProductName, 'Lumina or Nokia');

FreeText

Syntax
FREETEXT({column_name | (column_list) | *}, 'freetext_string');
Example
SELECT * FROM Product WHERE FREETEXT(ProductName, 'Lumina Nokia');

ContainsTable

Syntax
CONTAINSTABLE(table, {column_name | (column_list) | *}, 
	'contains_search_consdition');
Example
SELECT * FROM CONTAINSTABLE(Product, ProductName, 'Lumina or Nokia');

FreeTextTable

Syntax
FREETEXTTABLE(table, {column_name | (column_list) | *}, 
	'freetext_string');
Example
SELECT * FROM FREETEXTTABLE(Product, ProductName, 'Lumina Nokia');