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
–making this catalog as defaul
–DBO is the owner of AdvFTC catalog
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
–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),
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’
Select * from FTSTest
–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
–we are tracking change automatically
–you can do it manually also if you wish
–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.