SQL Server 2005 Full-Text Search Indexing

SQL Server 2005 Full-Text Search Indexing can be used with MS SQL server.We index a sample database and search it using Web based or Windows based application. To index a sample database we have to write some queries.

To do that I created a table named VTx in MS SQL Server 2005. If the database table contains "VideoText", "VideoTitle", and "VideoFrame" as columns and if we indexed it using "VideoText" column then we can write below steps to index it.

First I created the full-text catalog named VTxCatalog using below query.

CREATE FULLTEXT CATALOG VTxCatalog

Then I enabled the fulltext indexing by running the sp_fulltext_database

exec sp_fulltext_database 'enable'

Then I created the full-text index on table VTx

CREATE FULLTEXT INDEX ON VTx
(
VideoText

)
KEY INDEX PK_VTx ON VTxCatalog
WITH CHANGE_TRACKING AUTO

Then after inserting some records to the table, I was able to search it using below query

"SELECT * FROM VTx WHERE FREETEXT(Text, N'" + texttosearch + "')"

Using this query in whatever application, we can get the result set for the given word.