CONTAINSTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 6)



This is sixth 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 five articles in this series. Below are the links of the same.



Note: I am using the same table & data which I have created in my past articles. So if you are directly landed to this article, kindly get the create table script from second article listed above.

 Since we had already looked at FREETEXTTABLE, CONTAINSTABLE is not something new. It is also work at TVF but it has bit more functionality than FREETEXTTABLE. You have to provide table name, column(s) name and search condition. CONTAINSTABLE can accept all search condition which is valid in CONTAINS predicate.


CONTAINSTABLE also return KEY and RANK column like FREETEXTTABLE which you can join with your original table. You can refer FREETEXTTABLE’s article for more details.

Let us see some examples for CONTAINSTABLE in Microsoft SQL Server 2008.

–this T-SQL will return only key value and rank
SELECT
[KEY],
[RANK]
FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
);
GO

–this is sample search of CONTAINSTABLE
–with FORMSOF clause which we used in CONTAINS also in previous article
Select t2.* FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
) as t1 join FTSTest t2 on t1.[key]=t2.Id
GO

–there is one more useful clause, ISABOUT, which you can use with CONTAINS and CONTAINSTABLE
–You can give weight of the search word between 0.0 to 1.0
–search word with greater weight comes up with higher rank so that you can sort it easily.
SELECT
ct.[RANK],
ct.[KEY],
pm.[TestName],
pm.[TestDescription]
FROM CONTAINSTABLE
(
ftsTest,
TestDescription,
N’ISABOUT(volatile WEIGHT(0.1), General WEIGHT(1.0))’
) ct
INNER JOIN FTSTest pm
ON ct.[KEY] = pm.ID
ORDER BY ct.[RANK] DESC;
GO

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

CONTAINSTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 6)



This is sixth 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 five articles in this series. Below are the links of the same.



Note: I am using the same table & data which I have created in my past articles. So if you are directly landed to this article, kindly get the create table script from second article listed above.

 Since we had already looked at FREETEXTTABLE, CONTAINSTABLE is not something new. It is also work at TVF but it has bit more functionality than FREETEXTTABLE. You have to provide table name, column(s) name and search condition. CONTAINSTABLE can accept all search condition which is valid in CONTAINS predicate.


CONTAINSTABLE also return KEY and RANK column like FREETEXTTABLE which you can join with your original table. You can refer FREETEXTTABLE’s article for more details.

Let us see some examples for CONTAINSTABLE in Microsoft SQL Server 2008.

–this T-SQL will return only key value and rank
SELECT
[KEY],
[RANK]
FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
);
GO

–this is sample search of CONTAINSTABLE
–with FORMSOF clause which we used in CONTAINS also in previous article
Select t2.* FROM CONTAINSTABLE
(
FTSTest,
TestDescription,
N’FORMSOF(INFLECTIONAL, volatiles)’
) as t1 join FTSTest t2 on t1.[key]=t2.Id
GO

–there is one more useful clause, ISABOUT, which you can use with CONTAINS and CONTAINSTABLE
–You can give weight of the search word between 0.0 to 1.0
–search word with greater weight comes up with higher rank so that you can sort it easily.
SELECT
ct.[RANK],
ct.[KEY],
pm.[TestName],
pm.[TestDescription]
FROM CONTAINSTABLE
(
ftsTest,
TestDescription,
N’ISABOUT(volatile WEIGHT(0.1), General WEIGHT(1.0))’
) ct
INNER JOIN FTSTest pm
ON ct.[KEY] = pm.ID
ORDER BY ct.[RANK] DESC;
GO

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

FREETEXT Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 3)

If you have landed directly to this article and wish to have detail information about iFTS, I would recommend you to look at my previous two articles, Part1 and Part2 which covered basic information about iFTS.
FREETEXT predicate plays very crucial role in iFTS search. It is a basic yet very useful mechanism. This predicate searches a word that matches with inflectional forms and thesaurus.  You can use FREETEXT predicate on one column or on list of column inside your Full Text Index.
We have already created Full Text Catalog and Full Text Index along with one sample table in my previous article. We are going to use that table and index herewith in this article.
–this search will be performed on every column
–which are the part of our Full Text Index
–in our case, we have two columns (TestName, TestDescription)
–in our Full Text Index.
Select * from FTSTest where FREETEXT(*,N’Volatile’)
–following search will be performed on perticular columns only
Select * from FTSTest where FREETEXT(TestDescription,N’Volatile’)
Select * from FTSTest where FREETEXT(TestName,N’Volatile’)
–if you will try to use FREETEXT in the column which is
–not part of fullText Index, you will be greeted with error like belo.
–Msg 7601, Level 16, State 3, Line 1
–Cannot use a CONTAINS or FREETEXT predicate on column ‘Id’ because it is not full-text indexed.
Select * from FTSTest where FREETEXT(ID,N’Volatile’)
This is first step into the world of iFTS. See you soon with some more articles in this series only.
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

FREETEXT Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 3)

If you have landed directly to this article and wish to have detail information about iFTS, I would recommend you to look at my previous two articles, Part1 and Part2 which covered basic information about iFTS.
FREETEXT predicate plays very crucial role in iFTS search. It is a basic yet very useful mechanism. This predicate searches a word that matches with inflectional forms and thesaurus.  You can use FREETEXT predicate on one column or on list of column inside your Full Text Index.
We have already created Full Text Catalog and Full Text Index along with one sample table in my previous article. We are going to use that table and index herewith in this article.
–this search will be performed on every column
–which are the part of our Full Text Index
–in our case, we have two columns (TestName, TestDescription)
–in our Full Text Index.
Select * from FTSTest where FREETEXT(*,N’Volatile’)
–following search will be performed on perticular columns only
Select * from FTSTest where FREETEXT(TestDescription,N’Volatile’)
Select * from FTSTest where FREETEXT(TestName,N’Volatile’)
–if you will try to use FREETEXT in the column which is
–not part of fullText Index, you will be greeted with error like belo.
–Msg 7601, Level 16, State 3, Line 1
–Cannot use a CONTAINS or FREETEXT predicate on column ‘Id’ because it is not full-text indexed.
Select * from FTSTest where FREETEXT(ID,N’Volatile’)
This is first step into the world of iFTS. See you soon with some more articles in this series only.
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

Full Text Search in SQL Server 2005 Part 3

I have covered basic information about Full Text Search at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005.html

Configuring and maintaining Full Text Search Catalog at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005_16.html

And now finally I am going to show you how to select data after configuring the catalog with Full Text Search in Microsoft SQL Server 2005.

Contains:

Use Adventureworks

select * from HumanResources.Employee

WHERE Contains(HumanResources.Employee.LoginID,‘”*je*”‘)

I have used Double Quote and Asterisk as wild card as Full Text Search uses standard DOS convention as a wild card. Above query will return all the records whose “LoginID” column contain “je” anywhere in the data.

Note: I gave “LoginID” column name in CONTAINS but if you wish to search “je” in every column which has been added to index while creating catalog, you can use “*” there.

Use Adventureworks

select * from HumanResources.Employee

WHERE Contains(HumanResources.Employee.*,‘”*je*”‘)

FreeText:

Use Adventureworks

select * from HumanResources.Employee

WHERE freetext(HumanResources.Employee.*,‘jolynn0 david0’)

Contains uses exact word search while FreeText uses fuzzy or approximate word search. Above query will show all the records which either contain “jolynn0” or “david0”.

Reference: Ritesh Shah

Full Text Search in SQL Server 2005 Part 2

I will be showing how to configure and maintain Full Text Search catalog in Microsoft SQL Server 2005 in this article. However, if you are new to FTS and want to get basic idea about the same then you can refer my previous article at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005.html

Configuring Full Text Catalog (FTC):

You can use one FTC for one database, one database may have more than one FTC but you can’t use same FTC in other database. It is not sharable. FTC is nothing but just a collection of Full-Text indexes. One FTC may store more than one index for more than one table but each table can belongs to one FTC only. You can’t not create FTC on system table, table variable, views and temporary table.

You can configure Full Text Catalog by wizard of SSMS and from T-SQL Script. Since I am a script bee, I will explain T-SQL method.

–Enable database for FTS

use adventureworks

exec sp_fulltext_database ‘enable’

GO

–creating catalog with the name ‘FirstFTC-AdventureWorks

exec sp_fulltext_catalog ‘FirstFTC-AdventureWorks’, ‘Create’

GO

–mark table for full text search with primary key

exec sp_fulltext_table ‘HumanResources.Employee’,‘Create’,‘FirstFTC-AdventureWorks’,‘PK_Employee_EmployeeID’

GO

–add column to the catalog, NOTE: you can give more then one column also

exec sp_fulltext_column ‘HumanResources.Employee’,‘LoginID’,‘Add’

GO

–activate table for FTS

exec sp_fulltext_table ‘HumanResources.Employee’,‘activate’

GO

–run FTS index on table

exec sp_fulltext_table ‘HumanResources.Employee’,‘start_full’

GO

Wow, you have just successfully created one catalog for Full Text Search now what if you wish to maintain it.

You can maintain Full Text Search in two ways. “Incremental” and “Change Tracking and background population”

If you have Times stamp column in your table and you want to update your index at specific time, may be in the evening every day, you can use incremental.

If you wish SQL-Server to track back and keep watch on FTS enabled table and update the index automatically as and when data gets changed than you should go for “Change Tracking and Background population”.

— start incremental index update at specific time

exec sp_fulltext_table ‘HumanResources.Employee’,‘start_incremental’

GO

— start tracking and background update index automatically

exec sp_fulltext_table ‘HumanResources.Employee’,‘start_change_tracking’

exec sp_fulltext_table ‘HumanResources.Employee’,‘start_background_updateindex’

GO

–deleting catalog with the name ‘FirstFTC-AdventureWorks

exec sp_fulltext_catalog ‘FirstFTC-AdventureWorks’, ‘Drop’

GO

–rebuilding index ‘FirstFTC-AdventureWorks

exec sp_fulltext_catalog ‘FirstFTC-AdventureWorks’, ‘rebuild’

GO

Herewith, I am finishing this article and now finally my next article shows you how to select data with Full Text Catalog in Microsoft SQL-Server 2005

Reference: Ritesh Shah

Full Text Search in SQL Server 2005 Part 1:

While you think about text searching, LIKE operator come in your mind as you can search text within column with LIKE operator very easily in Microsoft SQL Server 2005. Suppose you have employee table than you may use below query to find first name:
Use AdventureWorks
Select * from Employee where FName LIKE ‘rite%’
GO
But what if you wish to search part of the word? You may go for following query.
Use AdventureWorks
Select * from Employee where FName LIKE ‘%rite%’
GO
Yes, you can use above given query if you wish to find any string data in column contained “rite” but it will terribly slow as Indexes are searchable from the beginning of the words. 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.
You can get rid of above problem by using FTS (SQL 2005 Full Text Search) which is third generation search component. You can find this tool with WorkGroup, Standard and enterprise version of SQL Server 2005.
Microsoft named this service as MSFTESQL (Microsoft Full Text Engine for SQL) which is disabled by default. You have to enable it in order to use it. I will explain you the benefit of the same with example and how to enable it.
First of all let me tell you how you can enable MSFTESQL services.
— Open Service area configuration
— Go to Surface Area Configuration for services and configuration
— You will find “Full-Text Search”, Click on “Start” if it is not started yet.

After running your MSFTESQL service, you are ready to use Full Text Search functionality of Microsoft SQL Server 2005. Once you enabled FTS, you will get advanced feature like wildcard search, search one word near another word, searching character data with embedded binary objects stored with SQL Server.
I will be writing series of articles for Full Text Catalog. My next article will cover creating and maintaining Full Text catalog.

Reference: Ritesh Shah