Expand News
IS Administration
Expand MMC
Expand Com Objects
Expand IDA/IDQ
IS Programming
Expand IS Programming
IS TroubleShooting
Expand Indexing
Expand Querying
IS Optimizing
Expand Querying
Expand Indexing
IS Links
Expand MSDN
Expand TechNet
Expand Other MS
Expand Other Links
Expand iFilters
SQL 2000 FTS
Expand Loading Data
Expand Indexing
Expand Querying
Expand SQL FTS Links
SQL 2005 FTS
Expand What's New

Index Server Faq

SQL Server 2000 shipped with Thesaurus capabilities which were disabled for use with the Contains predicate. They do work with FreeText however. 

If you made a typo while doing a FormsOf(Inflectional search in Contains you would get this interesting message:

Syntax error occurred near 'spelling_mistake'. Expected 'INFLECTIONAL, THESAURUS' in search condition 'formsof(spelling_mistake,run)'.

Which lead me to believe that it nearly shipped. Proding around I figured out how to do it.

Microsoft then released this kb article:


And with SP 2, you will not longer get this message, now the message is:

Server: Msg 7631, Level 15, State 1, Line 1 Syntax error occurred near 'spellingmistake'. Expected 'INFLECTIONAL' in search condition 'formsof(spellingmistake,test)'.

But the thesaurus functionality still works with FreeText search. It is unsupported by Microsoft and consequently should not be used in a production environment, as it is vulnerable to the next service pack, patch, and may have unpredictable consequences.

Review this kb article to understand how to implement it.


Keep in mind that for US English modify the tseng.xml file, and for the Queen's English, modify the tsenu.xml file. These files can be found in C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config

The good news is that Thesaurus capabiltiies are planned to ship in Yukon.

In the mean time if you need a supported solution to implement thesaurus capabilities to your web site have a look at this code sample give to me by SQL Server MVP Steve Kass (skass at drew.edu).

-- Data table

create table Hilary (

Item varchar(100) primary key,

Repl varchar(1000) default space(1) not null

--note we want ''

--make big enough for replacements ...

) -- Add trailing space to each junk word

insert into Hilary(Item) values ('the ')

insert into Hilary(Item) values ('in ')

insert into Hilary(Item) values ('a ')

insert into Hilary(Item) values ('an ')

insert into Hilary(Item) values ('on ')

insert into Hilary(Item) values ('for ')

insert into Hilary(Item) values ('to ')

insert into Hilary(Item) values ('of ')

-- Add trailing space to each replacement

insert into Hilary(Item,Repl) values ('spain','(spain or espa??a) ')

insert into Hilary(Item,Repl) values ('rain','(precipitation or rain or showers) ')

-- A table of integers is needed

create table Seq ( Nbr int not null )

insert into Seq

select top 4001 0 from Northwind..[Order Details]

cross join (select 1 as n union all select 2) X

declare @i int

set @i = 1

update Seq

set @i = Nbr = @i + 1


alter table Seq

add constraint pk_Seq primary key (Nbr)

--table Seq created


create function Spaces (@s varchar(8000)) returns table

as return select Nbr as Pos from Seq where substring(@s,Nbr,1) = space(1) and Nbr < len(@s)


create function Prepared_string (@s varchar(8000)) returns varchar(8000)



set @s = @s + space(1)

declare @out varchar(8000)

-- This is an undocumented trick, but I think it will

-- always work here. There are probably some safer

-- workarounds, but putting the tokens into a table and

-- rebuilding the output in a loop, a bit more slowly.

select @out = '' select @out = @out + coalesce(H.Repl,S.Item+space(1))

from ( select Pos, ltrim(rtrim(substring(@s,Pos+1, charindex(space(1),@s,Pos+1)-(Pos+1))))

as Item from dbo.Spaces(@s) ) S left join Hilary H on S.Item = H.Item

return @out




declare @x varchar(200)

set @x = 'The rain in spain falls mainly on the plain'

select left(@x,45) as StringIn, left(dbo.Prepared_string(@x),80) as StringOut





DROP FUNCTION Prepared_string


designed by :: smilla group