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:

http://support.microsoft.com/default.aspx?scid=kb;en-us;309678

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.

http://support.microsoft.com/default.aspx?scid=kb;en-us;837847

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

GO

alter table Seq

add constraint pk_Seq primary key (Nbr)

--table Seq created

go

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)

go

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

as

begin

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

end

go

--example

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

go

DROP FUNCTION Spaces

DROP TABLE Hilary

DROP TABLE Seq

DROP FUNCTION Prepared_string