Full text indexing - stemming October 22, 2008

A part of the full text indexing engine that I've never made much use of is the stemming aspect. When the full text index is generated for an object the words stored are parsed to remove any noise words (to see the noise words for English take a look at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt or similar - words such as about, another, and etc are considered as noise). The MS Full text index comes with the ability to match the stems of words - so if you were to index an object containing the word run, then a search looking for ran, runs, running etc should also match - basically the different tenses of the verb.

To try this out, create a full text index on a table and specify British English as the word breaker. I've created a simple table as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Word] [nvarchar](50) NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

My full text index is defined on the column [Word].

Enter some text into the indexed column(s), in my case, I ran the following insert statements:

INSERT INTO [test] ([Word]) VALUES ('run')
INSERT INTO [test] ([Word]) VALUES ('swim')
INSERT INTO [test] ([Word]) VALUES ('walk')
INSERT INTO [test] ([Word]) VALUES ('cycle')

Using the standard contains syntax, searching for 'run' as follows

SELECT * FROM Test
WHERE CONTAINS(Word, 'run')

returns
Id Word
----------- --------------------------------------------------
1 run

(1 row(s) affected)

If I search for 'ran' as follows:

SELECT * FROM Test
WHERE CONTAINS(Word, 'ran')

I get:

Id Word
----------- --------------------------------------------------

(0 row(s) affected)

probably as expected.

If I change the syntax to make use of the FORMSOF (INFLECTIONAL sytnax to look for 'ran' as follows:

SELECT * FROM Test
WHERE CONTAINS(Word, ' FORMSOF (INFLECTIONAL, ran) ')

I get:

Id Word
----------- --------------------------------------------------
1 run

(1 row(s) affected)

I get the same results if I look for 'run' or 'running' using the FORMSOF (INFLECTIONAL syntax, so this obviously improves the search results as the tense of verbs is no longer so relevant.