Lesson 1: Creating and Populating Full Text Indexes
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}
CREATE FULLTEXT INDEX ON table_name [ ( { column_name [ TYPE COLUMN type_column_name ] [ LANGUAGE language_term ] } [ ,...n] ) ] KEY INDEX index_name [ ON <catalog_filegroup_option> ] [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ] [;] <catalog_filegroup_option>::= {fulltext_catalog_name | ( fulltext_catalog_name, FILEGROUP filegroup_name ) | ( FILEGROUP filegroup_name, fulltext_catalog_name ) | ( FILEGROUP filegroup_name )} <with_option>::= {CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] } | STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }}
Quick Check
1. Before you can create a full text index, which structure do you need to create?
2. What do you need to specify to create a full text index on documents stored
Quick Check Answers
1. Full text indexes are contained within full text catalogs. Therefore, you must
2. SQL Server stores the documents within a VARBINARY(MAX) column with the
FILESTREAM property enabled. In order to create a full text index, you need to
also specify a type column that designates what type of document is stored in
the VARBINARY(MAX) column to load the appropriate fi lter for the word breaker
to use.
ALTER DATABASE AdventureWorks2008R2 ADD FILEGROUP AWFullTextFG GO ALTER DATABASE AdventureWorks2008R2 ADD FILE (NAME = N'S AdventureWorks2008R2FT', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2 FT.ndf') TO FILEGROUP AWFullTextFG GO
USE AdventureWorks2008R2 GO CREATE FULLTEXT CATALOG ProductsFTC ON FILEGROUP AWFullTextFG GO
Lesson Summary
Before creating a full text index, you must create a full text catalog that is mapped to a
fi legroup to contain one or more full text indexes.
You can create a full text index on CHAR/VARCHAR, XML, and VARBINARY columns.
If you create a full text index on a VARBINARY(MAX) column, you must specify the
column for the COLUMN TYPE parameter so that the full text engine loads the
appropriate fi lter for parsing.
The LANGUAGE setting controls the word breaker and stemmer that SQL Server loads
to tokenize and build infl ectional forms for the index.
Although a word breaker can be used against different languages that are closely
related with acceptable results, stemmers are specifi c to the language that is selected.
The CHANGE_TRACKING option controls whether SQL Server tracks changes to
underlying columns as well as whether changes are populated automatically into the
index.
Lesson 2: Querying Full Text Data
FREETEXT ( { column_name | (column_list) | * } , 'freetext_string' [ , LANGUAGE language_term ] )
SELECT ProductDescriptionID, Description FROM Production.ProductDescription WHERE FREETEXT(Description,N'bike') GO
FREETEXTTABLE (table , { column_name | (column_list) | * } , 'freetext_string' [ ,LANGUAGE language_term ] [ ,top_n_by_rank ] )
SELECT a.ProductDescriptionID, a.Description, b.* FROM Production.ProductDescription a INNER JOIN FREETEXTTABLE(Production.ProductDescription, Description,N'bike') b ON a.ProductDescriptionID = b.[Key] ORDER BY b.[Rank] GO
CONTAINS ( { column_name | (column_list) | * } , '< contains_search_condition >' [ , LANGUAGE language_term ] ) < contains_search_condition > ::= { < simple_term > | < prefix_term > | < generation_term > | < proximity_term > | < weighted_term > } | { ( < contains_search_condition > ) [ { < AND > | < AND NOT > | < OR > } ] < contains_search_condition > [ ...n ] }< simple_term > ::= word | " phrase " < prefix term > ::= { "word * " | "phrase *" } < generation_term > ::= FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] ) < proximity_term > ::= { < simple_term > | < prefix_term > } { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ] < weighted_term > ::= ISABOUT ( { { < simple_term > | < prefix_term > | < generation_term > | < proximity_term > } [ WEIGHT ( weight_value ) ] } [ ,...n ] )
SELECT ProductDescriptionID, Description FROM Production.ProductDescription WHERE CONTAINS(Description,N'bike') GO
SELECT ProductDescriptionID, Description FROM Production.ProductDescription WHERE CONTAINS(Description,N'"bike*"') GO
SELECT ProductDescriptionID, Description FROM Production.ProductDescription WHERE CONTAINS(Description,N' FORMSOF (INFLECTIONAL,ride) ') GO
SELECT ProductDescriptionID, Description FROM Production.ProductDescription WHERE CONTAINS(Description,N' FORMSOF (THESAURUS,metal) ') GO
SELECT a.ProductDescriptionID, a.Description, b.* FROM Production.ProductDescription a INNER JOIN CONTAINSTABLE(Production.ProductDescription, Description, N'bike NEAR performance') b ON a.ProductDescriptionID = b.[Key] ORDER BY b.[Rank] GO
SELECT a.ProductDescriptionID, a.Description, b.* FROM Production.ProductDescription a INNER JOIN CONTAINSTABLE(Production.ProductDescription, Description, N'ISABOUT (performance WEIGHT (.8), comfortable WEIGHT (.6), smooth WEIGHT (.2) , safe WEIGHT (.5), competition WEIGHT (.5))', 10) b ON a.ProductDescriptionID = b.[Key] ORDER BY b.[Rank] DESC GO
Quick Check
1. Which predicate performs fuzzy searching by default?
2. Which predicate is used to perform proximity and synonym searches?
Quick Check Answers
1. FREETEXT and FREETEXTTABLE predicates perform wildcard searches by default.
2. CONTAINS and CONTAINSTABLE are used for proximity, thesaurus, and infl ectional searches.
Lesson Summary
The FREETEXT and CONTAINS predicates return a value of True or False, which you can
then use in a query similar to an EXISTS clause to restrict a result set.
The FREETEXTTABLE and CONTAINSTABLE predicates return a result set that includes a
ranking column that tells you how closely a row matched the search term.
FREETEXT and FREETEXTTABLE perform wildcard searches by default.
CONTAINS and CONTAINSTABLE can perform wildcard searches along with proximity,
word form, and synonym searches.
Lesson 3: Managing Full Text Indexes
Quick Check
1. Which type of fi les enable searching based on synonyms?
2. What do you confi gure to exclude words from your index and search arguments?
Quick Check Answers
1. A thesaurus fi le allows you to confi gure synonyms for search arguments.
2. A stop list contains the list of words that you want excluded from a full text index as well as from search arguments.
SELECT ProductDescriptionID, Description FROM Production.ProductDescription WHERE CONTAINS(Description,N' FORMSOF (THESAURUS,metal) ') GO
Open the Tsenu.xml fi le (U.S. English) located at C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\FTData
<XML ID="Microsoft Search Thesaurus"> <!-- Commented out (SQL Server 2008) <thesaurus xmlns="x-schema:tsSchema.xml"> <diacritics_sensitive>0</diacritics_sensitive> <expansion> <sub>metal</sub> <sub>steel</sub> <sub>aluminum</sub> <sub>alloy</sub> </expansion> </thesaurus> --> </XML>
USE AdventureWorks2008R2 GO EXEC sys.sp_fulltext_load_thesaurus_file 1033; GO
SELECT ProductDescriptionID, Description FROM Production.ProductDescription WHERE CONTAINS(Description,N' FORMSOF (THESAURUS,metal) ') GO
PRACTICE 2 Build a Stop List
SELECT ProductDescriptionID, Description FROM Production.ProductDescription WHERE CONTAINS(Description,N'"bike*"') GO CREATE FULLTEXT STOPLIST ProductStopList; GO ALTER FULLTEXT STOPLIST ProductStopList ADD 'bike' LANGUAGE 1033; GO ALTER FULLTEXT INDEX ON Production.ProductDescription SET STOPLIST ProductStopList GO SELECT ProductDescriptionID, Description FROM Production.ProductDescription WHERE CONTAINS(Description,N'"bike*"') GO
Lesson Summary
You manage thesaurus fi les by editing the language-specifi c fi le that is contained
within the FTDATA directory for the instance.
You use the CREATE FULLTEXT STOPLIST and ALTER FULLTEXT STOPLIST commands to
build a list of stop words to be excluded from search arguments and the full text index.
Once a stop list has been built, you can use the ALTER FULLTEXT INDEX command to
associate a stop list with a full text index.
