How do I modify the thesaurus for full-text-indexing in SQL 2005?

Question

So I've read this: http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

And I've made the following modification to the tsENU.xml file to include the "programmer" and "programming" synonyms:

<XML ID="Microsoft Search Thesaurus">
    <thesaurus xmlns="x-schema:tsSchema.xml">
        <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            <sub>Internet Explorer</sub>
            <sub>IE</sub>
            <sub>IE5</sub>
        </expansion>
        <replacement>
            <pat>NT5</pat>
            <pat>W2K</pat>
            <sub>Windows 2000</sub>
        </replacement>
        <expansion>
            <sub>run</sub>
            <sub>jog</sub>
        </expansion>
        <expansion>
            <sub>programmer</sub>
            <sub>programming</sub>
        </expansion>
    </thesaurus>
</XML>

And as a last step I restarted the full-text-indexing service.

Everything I've read seems to indicate that this is all I should have to do. But alas, it doesn't affect the search results as I expect. Searching for "programming" in my database returns 59 results whereas "programmer" returns just 1. I'd expect the same number of results so I assume the server isn't actually updating the thesaurus.

Any ideas?

Answer 1

So I gave up on this problem with the server and solved it with code instead.

This isn't my idea of an ideal solution, but I just made my own thesaurus functionality in about an hour. And that's less time than I've spent researching this subject.

This is the VB.NET function I wrote:

  Public Shared Function GetFullTextSearch(ByVal strSearch As String) As String
    If strSearch > "" Then
      strSearch = Regex.Replace(strSearch, "\s\s+", " ").Trim.ToLower
      strSearch = Regex.Replace(strSearch, "[^\w\s]", "")

      Dim arrKeywords() As String = strSearch.Split(" ")
      Dim strFullTextSearch As String = ""

      Dim xpathDoc As XPathDocument
      Dim xmlNav As XPathNavigator
      Dim xmlNI As XPathNodeIterator

      Try
        xpathDoc = New XPathDocument(Current.Server.MapPath("~\bin\FullTextSynonyms.xml"))
        xmlNav = xpathDoc.CreateNavigator()
      Catch ex As Exception
        Current.Trace.Warn(ex.ToString)
      End Try

      For Each strKeyword As String In arrKeywords
        If strFullTextSearch > "" Then
          strFullTextSearch &= " AND "
        End If

        If Not xpathDoc Is Nothing Then
          xmlNI = xmlNav.Select("/Thesaurus/Synonyms[Synonym='" & strKeyword & "']/Synonym")
          If xmlNI.Count > 0 Then
            Dim strSearchOr As String = ""
            While xmlNI.MoveNext()
              If strSearchOr > "" Then
                strSearchOr &= " OR "
              End If
              strSearchOr &= "FORMSOF(INFLECTIONAL, '" & xmlNI.Current.Value & "')"
            End While

            If strSearchOr > "" Then
              strFullTextSearch &= "(" & strSearchOr & ")"
            End If
          Else
            strFullTextSearch &= "FORMSOF(INFLECTIONAL, '" & strKeyword & "')"
          End If
        End If
      Next

      Return strFullTextSearch
    Else
      Return Nothing
    End If
  End Function

And the corresponding custom thesaurus file:

<?xml version="1.0" encoding="utf-8" ?> 
<Thesaurus>
    <Synonyms>
        <Synonym>program</Synonym>
        <Synonym>programmer</Synonym>
        <Synonym>programming</Synonym>
    </Synonyms>
    <Synonyms>
        <Synonym>consult</Synonym>
        <Synonym>consultant</Synonym>
        <Synonym>consulting</Synonym>
    </Synonyms>
    <Synonyms>
        <Synonym>web</Synonym>
        <Synonym>website</Synonym>
    </Synonyms>
</Thesaurus>

In effect this creates a little more processing than may be necessary since in some cases I'm using the FORMSOF function multiple times on similar terms.

For example, when the user searches for "web consultant" then this is the actual full-text search that's passed into the CONTAINSTABLE function:

(FORMSOF(INFLECTIONAL, 'web') OR FORMSOF(INFLECTIONAL, 'website')) AND (FORMSOF(INFLECTIONAL, 'consult') OR FORMSOF(INFLECTIONAL, 'consultant') OR FORMSOF(INFLECTIONAL, 'consulting'))

Surely this isn't the most performant solution but it's still very fast with our database and functionally it's what I was looking for. Plus I now have the ability to modify my custom thesaurus file without the need to restart the full-text indexing service. But hey, if we upgrade to SQL 2008 later I can always give its thesaurus functionality a try as I believe it's better. Until then, this will work.