Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2)

I have provided introduction of iFTS in my previous article. If you want to get detail information bout iFTS, do read it.

Before we dive into the real world of iFTS, we need to prepare ourselves with some basic and essential substances. Those substances are

1.) Full Text Catalog

2.) Full Text Index.

Let us understand each in this session.

Full Text Catalog: FTC is nothing but just a container of the Full Text Index. Each Full Text Index can have only one FTC but each FTC could have more than one FTI. There is a facility in SQL Server 2008 that you can make FTC via T-SQL code as well as from SSMS. Since, I am script kid, I will go for T-SQL. BTW, there is one more reason to go for T-SQL, I am bit indolent to take so many screen captures and upload it in blog J

Note: I am using AdventureWorks sample database for this article, however, I am not going to use any table from it so you can use any of your database.

Here is the code for FTC:

--Creating FTC with standard T-SQL

--"CREATE FULLTEXT CATALOG"

--AdvFTC is a name of our Full Text Catalog

CREATE FULLTEXT CATALOG AdvFTC

--making our catalog as accent insensitive

--when we change this option,

--Full Text Index should be rebuild

WITH ACCENT_SENSITIVITY=OFF

--making this catalog as defaul

AS DEFAULT

--DBO is the owner of AdvFTC catalog

AUTHORIZATION dbo

Full Text Index: FTI plays very essential role in iFTS. Before you make full text index, let me tell you that you will need Single-Column Unique Index on that table so that your FTI can reference each and individual row separately. If you will try to make FTI on a table which doesn’t have Single-Column Unique Index, you will not be able to do so. If you will have composite primary key, even you won’t be able to make FTI as it has unique row with the combination of more than one column. It doesn’t guarantee that each piece of column will have different value.

Before we create FTI, let us create one table for demonstration with some DUMMY data.

--Creating one table for demonstration

--table will list of Test being performed by

--environmental laboratory

--just for demonstration purpose, not a real data

--I am not environmental engineer so test or description

--could be wrong :)

Create Table FTSTest

(

      Id int identity(1,1),

      TestName varchar(50),

      TestDescription Varchar(max)

)

ALTER TABLE FTSTest Add Constraint PKftstestID Primary Key (ID)

Insert Into FTSTest

Select 'Alkalinity','This test is being performed for General Chemistry' Union ALL

Select 'Cynide','This test is again belongs to General Chemistry department' Union ALL

Select 'Hexavalent Chromium','Ohh god, again general chemistry test!!!' Union ALL

Select 'TCLP VOA', 'This test is belongs to Volatile department' UNION ALL

Select 'VOC-TABF','Again Volatile department test' Union All

Select 'TO-15','Volatile department test for AIR'

GO

Select * from FTSTest

GO

--creating fulltext index on FTSTest table

CREATE FULLTEXT INDEX ON FTSTest

(

      --following two column will take part in

      --full text index. you can have maximum 1024 column

      --in this index

      TestName LANGUAGE ENGLISH,

      TestDescription LANGUAGE ENGLISH

)

--key index clause specify PK of table

KEY INDEX PKftstestID ON

(

      AdvFTC

)

WITH

(

      --we are tracking change automatically

      --you can do it manually also if you wish

      CHANGE_TRACKING AUTO

)

--after creating index, making it enable

Alter FULLTEXT INDEX ON FTSTest ENABLE;

Now, you are ready to use full featured iFTS. We will meet again with real practice of iFTS in next article.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com