Full-text Search and Indexes

The Standard and Enterprise editions of SQL Server include SQL Server FullText Search service, a search engine that allows full-text indexing and querying like the search engines used to query the Web. You can search for combinations of words and phrases. It allows linguistic searches whereby the engine also matches variations of the original word (singular, plural, tenses, and so on). The result may be a simple list or a table that ranks how well the results match the search criteria. Part of the criteria may also be the proximity of words and phrases—that is, how close one word is to another.

These capabilities are different from those of standard database search engines, in which you can do the following:

• Search for an exact match of a word or phrase.

• Use wild card characters and the Like operator to search for character patterns.

• Use indexes only if a pattern matches the beginning of the field.

SQL Server FullText Search service was first introduced as a component of Internet Information Server. At that time, it was called Index Server.

I will not go into the details of SQL Server FullText Search service's architecture and administration, except to note that

• Full-text indexes are not stored in databases but in files (usually in C:\Program FilesMVIicrosoft SQL Server\MSSQL\MSSQL.x\FTDATA).

• You have to set the full-text index search capability and create full-text catalogs on tables and columns explicitly.

• A table must have a unique index based on a single field to be indexed in this manner.

  I will focus on full-text search capabilities. The two most important predicates are Contains and Freetext. They are designed to be used in the Where clause of a Select statement.

The Contains predicate returns true or false for records that contain specified keywords. Variations of this predicate accommodate linguistic searches for variations of words and for words that are in proximity to other words. For more details, see SQL Server Books OnLine.

Freetext orders the search engine to evaluate specified text and extract "important" words and phrases. It then constructs queries in the background to be executed against the table.

The following stored procedure implements different forms of full-text search on the ActivityLog.Note field:

      Alter Procedure ap_FT_ActivityLog_Search      -- full-text search of ActivityLog.Note      -- this will only work if you enable full-text search           (                 @chvKeywords varchar(255)                 @inySearchType tinyint            )      As      set nocount on      -------- Constants ----------      declare    @c_Contains int,                 @c_FreeText int,                 @c_FormsOf int      Set        @c_Contains = 0 Set                 @c_FreeText = 1 Set                 @c_FormsOf = 2      -------- Constants ----------      if @inySearchType = @c_Contains          exec ('select * from dbo.Activity Where Contains(Note, '               + @chvKeywords + ' ) ' )      else if @inySearchType = @c_FreeText          exec ('select * from dbo.Activity Where FreeText(Note, '               + @chvKeywords + ') ' )      else if @inySearchType@inySearchType = @c_FormsOf          exec ('select * from dbo.Activity '               + 'Where FreeText(Note, FORMSOF(INFLECTIONAL,'               + @chvKeywords + ')')      Return 

Note 

Full-text search has additional features related to the use of ContainsTable and FreeText table and the use of the Formsof, Near, and Weight keywords, hut the description of these features is beyond the scope of this chapter and this book.