April 24, 2018

SQL 2005 - Creating and Using Full-Text Index

1. Introduction – Full Text Search Queries


The Full Text Search Queries can be enabled in Microsoft SQL 2005 and later versions. Lets us consider an online shopping web site storing products in their database. Let us also assume that the ProdDetails column can store texts of 1000 Words. Now, when user searches for a specific product details, the query will end up applying a string search on the ProdDetails. If the site supports millions of products, the search will take long time.


The good solution is creating a “Full Text Index” on that column and that not only makes the search faster but also provides rich search functionality on that columns. In this Article, we will first see how to enable the full text search on a particular column and then look at some most frequently used “Full Text Search Queries”.

2. Database and Table used for this Example

In this example, we are going to use, “notes” column of the “titles” table from the “pubs” database. One can setup the pubs database by downloading it through a web search or the database creation script available in the SQL 2005 section of this web site.

Title Table Of Pubs Database
Title Table Of Pubs Database

3. Creating Full Text index (Step-By-Step) for Notes Column


To create a Full-Text Index, right click on the Titles table and select “Define Full-Text index…” from the context menu as shown in the below picture.

Create Full Text Index ContextMenu
Create Full Text Index ContextMenu

This will start a welcome screen titled “Full-Text Indexing Wizard”. Go through the instructions given and Click Next.

Full Text Indexing Wizard Welcome Screen
Full Text Indexing Wizard Welcome Screen

The below screenshot shows next series of wizard pages. In the “Select an Index” page, we are selecting the primary key on the table. For a Full-Text index the table should have at least one Primary Key or Unique Key. In our case, the title_id column is a primary key and hence we selected that.

In the “Select Table Columns” page, we are actually selecting the column which require Full-Text Search functionality. In our case, we selected the column “notes”. Then we accepted the defaults for the “Select Change Tracking” page as we need automatic tracking of the index. The option “Automatic” specifies that changes made to the Notes column is tracked so that the index will be in-sync with the data always.

In the “Select Catalog” screen, we created a new catalog called Titles-Notes. We can use one catalog to store one or more Full Text indexes. Here, we created one catalog for our Notes Column Full-Text index. Accept the defaults for other wizard pages and click finish on the final page. This will create a Full Text Search index for the notes column and enable support for Full Text Search queries. Now, let us look at some Full Text Search query examples.

Full Text Indexing Wizard Pages
Full Text Indexing Wizard Pages

The complete Settings are Shown in the below video:



4. Full Text Search - Examples


OK. Now we have enabled Full Text Search Support for the notes column. Now, let up explore full text search queries.

4.1 CONTAINS with AND


The CONTAINS Form takes two parameters. First one is the Full-Text indexed column and the second parameter is the search strings. Below is the example query which uses CONTAINS:

Full Text Query Contains with AND
Full Text Query Contains with AND

In this example, we are using the CONTAINS form full text query (Marked as 1) and passing the column name “notes” as the first parameter (Marked as 2). The search terms are passed through second parameter. Note how three search terms are specified within double quotes and the entire second parameter is given as single quoted string. The query will look for all three terms and returns the result.


4.2 CONTAINS with Generation Terms



Now, consider the below example. Here, even though the notes column is not having any row with a term live, The “FormsOf” with “INFLECTIONAL” helped to find a row with the nearest meaning. Here, living is a generation term from the word Live and hence we see that in the Query output. 

Full Text Query Contains with FormsOf
Full Text Query Contains with FormsOf

Below is one more example which brings result as it finds Researched is a Form of Research. Note that out table has limited rows. It will be useful with the table with multiple rows and will make search easier as it handles generation terms. For example, if we search for write, we will get all rows that contains either write, wrote, written and writing.

Full Text Query Contains with FormsOf 2
Full Text Query Contains with FormsOf 2

Below is last example on the FormsOf. Here, we combined two FormsOf within a single CONTAINS search.

Full Text Query Contains with FormsOf 3
Full Text Query Contains with FormsOf 3

4.3 CONTAINS with Prefix Form




The prefix form is used to specify a word(s) that starts with a specific character. In the below example, we bringing the result rows which has one or more words that starts with “Com” and this specified as “Com*”



Full Text Query Contains with PrefixForm
Full Text Query Contains with PrefixForm

The Prefix can be applied for more than one word also. For Example, in the below query the term “com hard*” apply prefix for both the words. That is, it looks for word starting with “hard” as well as “com”.

Full Text Query Contains with PrefixForm2
Full Text Query Contains with PrefixForm2

4.5 Contains with NEAR Proximity 


The Proximity form of the CONTAINS is used to return the results when two specified terms are near to each other. How much is decided by the Microsoft SQL Server. 

Full-Text-Query-Contains-with-NEAR-Proximity
Full-Text-Query-Contains-with-NEAR-Proximity

4.5 The FREETEXT filter


When the rule is not strict and if we want to bring the result based on some search terms, then "FREETEXT" is the solution for it. As the name suggests, only Microsoft knows how it filters the result based on the given FREETEXT. The FREETEXT form of the Full-Text Search query takes some time to bring the result. Below is an Example:

Full-Text-Query-FREETEXT
Full-Text-Query-FREETEXT

Once experimented, if you want to delete the Full-Text search index, use the same context menu in which we created the index. The complete Listing is below:

-- Example 1 (Simple Form)
Select notes from titles
where contains(notes, '"medical" AND "psychological" AND "electronic office" ');

--Example 2 (Generation Terms)
Select notes from titles
where contains(notes, 'FormsOf(INFLECTIONAL, Live)');
Select notes from titles
where contains(notes, 'FormsOf(INFLECTIONAL, live)');
Select notes from titles
where contains(notes, 'FormsOf(INFLECTIONAL, Research)');

--Example 3 (Prefix Form)
Select notes from titles
where contains(notes, '"com*"');

Select notes from titles
where contains(notes, '"com hard*"');

--Example 4 (Proximity Terms)
Select notes from titles
where contains(notes, 'Software NEAR Computer');

--Example 5 (FreeText)
Select notes from titles
where FreeText(notes, 'Software describe Japanese microwave cooking');



No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Friend :)