Full Text Index in SQL Server

If we have varbinary(max), or varchar(max) columns in the sql server table we cannot create indexes on those columns using the standard clustered or non-clustered indexes. The Full Text Index is the solution for this. Full Text Index can be achieved through full text search feature in SQL Server. It helps to run the full-text queries against character based data in sql server tables.


Steps

1. Create a full text catalog

Use DatabaseName
Create FullText Catalog MyFTCatalog
Select fulltext_catalog_id, name from sys.fulltext_catalogs

After creating full text catalog we need to create full text index on the required table. To create full text index, Table must be having unique index. If you don’t specify the catalog name while creating full text index then it will attach to default catalog.

2. Create full Text Index

Create FullText Index on Items(ItemDescription, ItemContent Type Column ItemExtention Language 1033)
Key Index Ix_Item_ID on MyFTCatalog

Select * from sys.fulltext_languages – Full Text Supported Languages.
Select * from sys.fulltext_document_types – Displays list of document types supported by full text search.

Below Query is to display list of catalog names and table name in the catalog

SELECT st.name AS TableName, sc.name AS FTCatalogName
FROM sys.tables st, sys.fulltext_indexes si, sys.fulltext_catalogs sc
where st.object_id = si.object_id and si.fulltext_catalog_id = sc.fulltext_catalog_id

Select * from Items
Where contains([ItemContent], '"*Microsoft*" OR "*SQL Server*"');

Select * from Items
Where FreeText([ItemContent], '"*Microsoft*" OR "*SQL Server*"');