FREETEXTTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 4)

This is fourth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past three article in this series. Below are the links of the same.

BTW, I am going to use the same table I have created in my previous article so for practical exercise get the table and script from that article if you are directly landing to this page.
If you have read above articles than you must be clear with FREETEXT predicate. Now, we will continue over the topic of this article.
If you are SQL Server developer then you might know TVF. FREETEXTTABLE is working something like it. You have to use FREETEXTTABLE in FROM clause only, just like a regular table or TVF. It will accept three things as below.
1.)    Table Name
2.)    Column Name(s)
3.)    Search Text
It will not return any row with real value rather it will return two columns.
1.)    [KEY]
2.)    [RANK]
First column [KEY] will return the key value of the Index column you have in your original table so that you can join this [KEY] column to your original table and get real full row whenever it is needed.
Second column [RANK] will return relevance ranking of the search text in the column(s) you have specified in FREETEXTTABLE.
So now this is really enough theory, isn’t it? Let’s have some real practical task.
–as per explaination given above,
–below query will return just key and rank. you can confirm
–whether you get right key or not based on your search criteria
select [Key],[Rank] from FREETEXTTABLE(FTSTest,TestDescription,‘Volatile’)

–just commpare [KEY] with ID column of our FTSTest
Select * from FTSTest

–now we can get real value by making join of FREETEXTTABLE and FTSTest
SELECT T2.* FROM FREETEXTTABLE(FTSTest,TestDescription,‘Volatile’) as t1 JOIN FTSTest as T2
ON T1.[KEY]=T2.Id
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
Microsoft SQL Server Blog. Fight the fear of SQL with Founder is Ritesh Shah

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
–AdvFTC is a name of our Full Text Catalog
–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
–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’
Select * from FTSTest
–creating fulltext index on FTSTest table
      –following two column will take part in
      –full text index. you can have maximum 1024 column
      –in this index
      TestDescription LANGUAGE ENGLISH
–key index clause specify PK of table
      –we are tracking change automatically
      –you can do it manually also if you wish
–after creating index, making it 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
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
Microsoft SQL Server Blog. Fight the fear of SQL with Founder is Ritesh Shah

Introduction of iFTS (Integrated Full Text Search – iFTS in SQL Server 2008 Part 1)

Full Text Search is not a new concept in SQL Server 2008. It has just some advanced feature and better architecture and that is why it is known as iFTS rather than FTS in SQL Server 2008.
FTS is nothing but just one of the powerful tool in SQL Server. SQL Server is a set based technology and very well known for its capability to handle big amount of data. It has powerful capacity for different data related operation like Insert, Update, Delete and Select. FTS is used for last operation “SELECT” most of the time.
Well after reading above two paragraphs, you might think that why we need FTS or iFTS since we are already having enough stuff to use in WHERE clause. But if you will practically see, what do you if you want to search column which has certain words inside it? Of course you will go for LIKE operator. Isn’t it?
Select * from emps where FullName LIKE ‘%Ritesh%’

If you will look at execution plan of above query, you will definitely see full table scan, even if you will have index on FullName column of emps table, because regular index works on predicate bases. Searching string within string won’t use B-Tree structure of an index to perform fast index search rather it scan full table and will slow up your search.
So, this is all we have talked about FTS, what iFTS means? Is it something new creature in the world of SQL Server? No, my dear friend, it is new wine with old bottle I can say.
In iFTS (Integrated Full Text Search), Microsoft has tried to improve some old limitations. I am listing some of them below.
à Full Text Search is now a part of SQL Server Query Engine itself. Before It was separate service called MSFTESQL (Microsoft Full Text Engine for SQL), it was creating bit overhead to communicate with each other which is eliminated as now FTS is a part of main SQL Server Query engine.
àNoise word of FTS is now come up with new terminology “Stop List” in SQL Server 2008. It is not being stored in separate text file rather it stored in database itself.
àIn SQL Server 2008, you can change stop list and Thesaurus list before even restarting SQL Server Services.
So, these are few of the benefit and change you will get in iFTS over FTS. I guess you guys are now prepared to learn iFTS. Let us start our journey from next article.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
Microsoft SQL Server Blog. Fight the fear of SQL with Founder is Ritesh Shah