SQL Server 2008 Integrated Full Text Search – Part 1

Hello Folks,

You have been well known about the iFTS previously. Well, if somehow you don’t, then you should not be worry about this anymore!!

History…a little bit 🙂

SQL Server 2008 Integrated Full Text Search was introduced in late 1998 when Microsoft reengineered one of its search engines, so as to provide search services for SQL Server 7. The engine was so called MSSearch and is also provided search services to Exchange Content Indexing and SharePoint Portal Server 2001.

Microsoft were been continued to improve iFTS’s performance and scalability with SQL Server 2000 and SQL Server 2005.

The SQL Server Development Team at Microsoft has been doing some great work with Integrated Full Text Search (iFTS) in SQL Server 2008. The old stored procedure methods of setting up Full-Text Search are deprecated, i.e., they will be removed soon.

Features of SQL 2008 iFTS:

• SQL 2008 iFTS is the fourth-generation search component for SQL Server, and it is by far the most scalable and feature-rich version.
• SQL 2008 iFTS comes with the Workgroup, Standard, and Enterprise versions of SQL Server.
• From now onwards, SQL Server is no longer dependent on the indexing service of Windows.
• The integration of FTS in the SQL engine also result in better performance because the Query Optimizer can make an informed decision whether to invoke the full-text engine before or after applying non-FTS filters.
• There are many new DMVs expose the workings of iFTS.
• There are now 40 new languages.

iFTS Catalogs:

• A full-text search catalog can be defined as a collection of full-text indexes for a single SQL Server database.
• Here, each one of the catalog can store multiple full-text indexes for multiple tables and at the same time each table is limited to only one catalog.
• A single catalog can handle all the full-text searches for a database, and also enhances the performance when a very large table is being depending upon the single catalog.
• Therefore, the Catalogs can be created by two ways-
  • With the Wizard.
  • With T-SQL code.
• Well I have used here “School” database, which I have created for this article.

Creating a Catalog with the Wizard:

So, see the following steps to create a catalog-

1. Select “Define Full-text Index”

2. The Full-Text Indexing Wizard windows will shows up. Click on Next.

3. Select a unique index that full-text can use to identify the rows indexed with full-text. The primary key is typically the best choice for this index, if not any non-nullable, unique, single-column index is sufficient. In this case, we have used “PK_Class” as a Unique Index. Click on the Next button.

4. Choose the columns to be full-text indexed. The valid data types include character data types and binary data types. See the following columns that have been selected:

5. Select the Change Tracking Mechanisms out of “Automatically, Manually, and Do not track changes”. Automatically – It means that Change Tracking is enabled and automatically updated. Manually – It means that updates are manual but change tracking is still enabled. While the change tracking can also be completely disabled.

6. Now, you can select an existing full-text catalog or create one for this database. You can also select the Index Filegroup, and Stoplist.

7. Skip for the creation of a population schedule. There’s still a better way to keep the catalog up-to-date.

8. Click on the Finish button.

9. The Screen will flash up:

Creating a Catalog with the T-SQL code:

The alternative to create the catalog instead of the above 9-steps can cut-down to a SQL script which uses DDL CREATE statements. See the following query;

USE School
CREATE FULLTEXT CATALOG SchoolF;
CREATE FULLTEXT INDEX ON dbo.Class(Name, Sub, Fav_Sub)
KEY INDEX PK_Class ON SchoolF
WITH CHANGE_TRACKING AUTO;

Further, if you want to alter full text index, so you can use the statement:

USE School;
ALTER FULLTEXT INDEX ON Class START FULL POPULATION;

If you want to remove a full-text catalog, so you can use the statement:

USE School;
DROP FULLTEXT INDEX ON dbo.Class
DROP FULLTEXT CATALOG SchoolF

Well, this was all about how to create catalogs for iFTS.

In the next article post, I will deal with Contains and ContainsTable function of iFTS.

Hope you got it understood well 🙂

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook