SQL Server Full Text Search Example

2017/1/43 min read

Example of setup the full-text search on SQL Server

SQL Server full-text search requires installing on SQL Server, after install the full-text search you can setup the index as follows.

How to check Full-Text Search is enable at SQL Server?

If following return 1 means enabled, 0 means is not.

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

How to get all full text indexed column

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 setup 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 key word Surface Pro


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

If you search for a column is not full-text indexed or SQL Server is not installed full-text feature, you'll see following errors.


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 use for test 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 Text and Text2 columns the o use full-text index, we can add as following.

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 the BUY at the Text2 column will return all the sterming word, such as buy, bought, buys.


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

Reference

SQL