CONTAINS acts weird?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roel Korsten - 21 Jun 2006 15:48 GMT

Hi all,

I'm a bit lost in my Full_Text_Search_Adventure:

I've got a PDF-document in a SQL2005 table in which the following line
resides:
... homer is a computer geek ...

I'm querying the following statement:

SELECT [fieldname]
FROM [table]
WHERE CONTAINS([indexed_fieldname],' "homer" AND "is" AND "a" AND
"computer" AND "geek" ')

which gives no results.

The index is correct because

SELECT [fieldname]
FROM [table]
WHERE CONTAINS([indexed_fieldname],' "homer*" AND "is*" AND "a*" AND
"computer*" AND "geek*" ')

gives the right results.

Greetings,
Roel

Reply to this Message

Daniel Crichton - 21 Jun 2006 17:06 GMT

Roel wrote on Wed, 21 Jun 2006 16:48:57 +0200:

> Hi all,
>
[quoted text clipped - 11 lines]
>
> which gives no results.

If you run this in Query Analyzer, do you get a message about the query
containing ignored words? If so, it's ignoring the entire CONTAINS clause
because at least one word is in the ignore list. Why MS made it do this
rather than just skip the ignored words and search the index for the
remaining words is a mystery.

I ended up emptying the noise words file on my server and rebuilding the
index, that way everything gets indexed and I don't notice a performance
difference.

Dan

Reply to this Message

Roel Korsten - 22 Jun 2006 08:18 GMT

Hello Daniel,

Thanx for your reply. I ended up at
http://www.simple-talk.com/2006/04/21/understanding-sql-server-full-text-search-
part-i/
and found out the existence of "transform noise words option". When you
search on this in BOL you'll see what it does:

<quote>
... When the transform noise words option is set to 1, SQL Server replaces
noise words with the asterisk (*) in phrase queries. ...
</quote>

After applying this setting, everything is working fine!

Greetings,
Roel

> Roel wrote on Wed, 21 Jun 2006 16:48:57 +0200:
>
[quoted text clipped - 26 lines]
>
> Dan

Reply to this Message

Daniel Crichton - 22 Jun 2006 08:35 GMT

Roel wrote on Thu, 22 Jun 2006 09:18:39 +0200:

> Hello Daniel,
>
[quoted text clipped - 8 lines]
>
> After applying this setting, everything is working fine!

Interesting, as Hilary noted in that article that it appears to have no
effect, and this is only a SQL Server 2005 setting. Also reading just above
there it says that SQL Server 2005 should automatically drop noise words and
only if all the words being search for are noise words should it throw an
error.

I myself have never had this issue with SQL Server 2005 as my original FTS
was built on SQL Server 7 where I had a choice of removing all the noise
words, or writing a search parser to remove all the noise words from the
query, and I went with the former as it allowed for much more accurate
searches. When I moved to SQL Server 2005 I just cleared the noise word file
before building my FTI, and I get really good responses (most searches take
less than a second on a table of nearly 500,000 bibliographic records).

Dan

Reply to this Message