Creating full-text indexes on custom tables and views

Introduction

Although Smartsite iXperion Faceted Search is capable to handle native queries, it is highly recommended to use the database's text-indexing technology like FTS for SQL Server. Using such a technology not only affects performance, but also uses linguistic rules like stemmers, thesauri, noice words and word breakers in order to optimize the results.

Note: native queries in Faceted Search will not automatically take stemmers, thesauri, noice words (stopwords) or word breakers into account. Native queries typically use LIKE statements without any linguistic rules. Furthermore, if the used collation is case sensitive and/or accent sensitive, this typically have a negative impact on search results, especially for languages like English or Dutch. Although native queries supports ranking, the used ranking algorithm in FTS is superior.

The rest of this page describes how to create, maintain and use full-text indexes on custom tables, that is, other tables besides Contents. For the time being only SQL Server's FTS/iFTS is explained here.

Full-text vs Native

A simple comparison between full-text and native queries within Faceted Search:

Prerequisites

In order to be able to create a full-text index on a database object, such an object must meet to a critical requirement: only tables and views with a clustered unique index (or primary key) on exactly one column are allowed to be fulltext indexed.

If a view is to be indexed, it must contain a clustered unique index on exactly one column, meaning this view must be a schema-bound indexed view. (Or materialized view in Oracle terms.) Note that an indexed view will implicitly be dropped if its parental table (or one of them) is altered (e.g. add column, alter column, drop column).

Only binaries and textual columns can be indexed by FTS, thus a table or indexed view without any textual or binary columns cannot be fulltext indexed.

A table or indexed view can only have one fulltext index.

Creating the fulltext index

Creating the fulltext index on a table or indexed view can be done by running the following query:

SQL	Copy Code
CREATE FULLTEXT INDEX ON FileTypes (Name LANGUAGE 1043, Code LANGUAGE 1043) KEY INDEX PK_FileTypes ON SmsSearch;

In this example the columns Name and Code of the table FileTypes will be fulltext indexed in Dutch.

Querying the fulltext indexes and catalog

To determine whether a table or indexed view is correctly fulltext indexed in the correct fulltext catalog, the following query can be used:

SQL	Copy Code
SELECT ftc.name AS FTSCatalog, OBJECT_NAME(object_id) AS IndexedObject FROM sys.fulltext_indexes fti INNER JOIN sys.fulltext_catalogs ftc ON fti.fulltext_catalog_id = ftc.fulltext_catalog_id;

When successfully running the first query, you should see FileTypes as an indexed object.

Using FTS

Please refer to the technical background information for more information about FTS usage. A couple of examples of queries which can be used in Faceted Search's Query Definition XML follow:

SQL	Copy Code
SELECT * FROM FileTypes WHERE FREETEXT(*, 'templates 2007'); SELECT * FROM FREETEXTTABLE(FileTypes, *, '2007 templates') ftt INNER JOIN FileTypes ft ON ftt.[KEY] = ft.Nr;

Query Definition XML

In the Query Definition XML, enrich the sources tag with an xml block like:

XML	Copy Code
<source name="filetypes" displayname="File Types" enabled="true"> <searchbases> <searchbase> <query><![CDATA[ SELECT ft.Nr, SUM(tsft.[RANK]) as SearchRank, 1 AS BaseSearch FROM CONTAINSTABLE(filetypes, {this.fieldname(default="*")}, ?:searchterm) AS tsft JOIN filetypes ft ON tsft.[KEY] = ft.Nr ]]> </query> </searchbase> </searchbases> </source>

Please refer to these pages for more examples and explanation regarding Query Definition XML.

Note that when Smartsite's Web Toolkit is installed on the targeted Smartsite instance prior to installing Faceted Search, the newly introduced swtItemComments will be added as a fulltext index on SmsSearch. Please review its Query Definition XML for a complete example of a custom fulltext index within Faceted Search.