SQL Server Full Text Search Example

Table of Contents

• Introduction
• How to Check If Full-Text Search Is Enabled on SQL Server
• How to Get All Full-Text Indexed Columns
• How to Set Up an Index for Specific Tables
• How to Query Data by Full Text
• Walk Through with Example
• Reference
• Conclusion

Introduction

SQL Server full-text search requires installation on SQL Server. After installing full-text search, you can set up the index as follows.

How to Check If Full-Text Search Is Enabled on SQL Server

If the following returns 1, it means it is enabled; 0 means it is not.

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

How to Get All Full-Text Indexed Columns

select t.name, sc.name
from
    sys.fulltext_index_columns ft
    inner join sys.columns sc
        on sc.[object_id] = ft.[object_id]
        and sc.[column_id] = ft.[column_id]
	join sys.tables  t   ON sc.object_id = t.object_id

How to Set Up an Index for Specific Tables

CREATE FULLTEXT CATALOG MyFullTextSearch;
CREATE UNIQUE INDEX MyIndex ON MyTable(TheUniqueKey);
CREATE FULLTEXT INDEX ON MyTable.MyDescriptionColumn
(
    MyDescriptionColumn
        TYPE COLUMN FileExtension
        Language 1033
)
KEY INDEX MyIndex ON MyFullTextSearch
WITH CHANGE_TRACKING AUTO
GO

CREATE FULLTEXT CATALOG MyFullTextSearch;

CREATE UNIQUE INDEX MyIndex ON MyTable(Id);

CREATE FULLTEXT INDEX ON MyTable(MyColumn)
KEY INDEX MyIndex ON MyFullTextSearch
WITH CHANGE_TRACKING AUTO
GO

How to Query Data by Full Text

The following query will search MyTable's MyColumn for the keyword Surface Pro.

select * from MyTable where FreeText(MyColumn, 'Surface Pro')

If you search a column that is not full-text indexed or SQL Server does not have the full-text feature installed, you'll see the following error:

select * from MyTable where FreeText(MyColumnNotFullTextIndexed, 'Surface Pro')

Msg 7601, Level 16, State 2, Line 15
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'MyColumnNotFullTextIndexed' because it is not full-text indexed.

Walk Through with Example

Create a Table for Testing Full-Text Index

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestFullText](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Text] [nvarchar](max) NULL,
	[Text2] [nvarchar](max) NULL,
 CONSTRAINT [PK_TestFullText] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO
SET IDENTITY_INSERT [dbo].[TestFullText] ON

GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (1, N'I am your father, boogle', N'I buy')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (2, N'father boogle', N'he buys')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (3, N'farther', N'they buy')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (4, N'Cooking', N'I bought')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (5, N'cooks', N'I''m buying')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (6, N'COOK', NULL)
GO
SET IDENTITY_INSERT [dbo].[TestFullText] OFF
GO

Add Full-Text Index to the Table

Assume we'd like to use the Text and Text2 columns for full-text indexing. We can add the index as follows:

CREATE FULLTEXT CATALOG MyTestFullTextTableTextSearch;
CREATE UNIQUE INDEX MyIndex2 ON TestFullText(Id);
CREATE FULLTEXT INDEX ON TestFullText(Text, Text2)
KEY INDEX MyIndex2 ON MyTestFullTextTableTextSearch
WITH CHANGE_TRACKING AUTO
GO

Run Full-Text Index Query

As you can see, the full-text search for "BUY" in the Text2 column will return all the stemming words, such as buy, bought, and buys.

select * from TestFullText where Text2 like '%buy%'
  select * from TestFullText where FreeText(Text2, 'buy')
  select * from TestFullText where Contains(Text2, 'buy')

Reference

• MSDN - Full-Text Search
• MSDN - CREATE FULLTEXT INDEX (Transact-SQL)
• MSDN - Create and Manage Full-Text Catalogs
• Create and Manage Full-Text Indexes
• MSDN - Get Started with Full-Text Search
• MSDN - Query with Full-Text Search
• Blog - SQL vs No.SQL
• MSDN - Local ID
• Blog - MongoDB Text Search

Conclusion

SQL Server's full-text search feature enables efficient searching of text data using stemming, linguistic analysis, and more. By creating a full-text catalog and index on your desired columns, you can use FreeText and Contains predicates for powerful text queries that go beyond simple LIKE pattern matching.