Some gotchas I have come accross to date:
a) Server: Msg 7619, Level 16, State 1, Procedure SearchOne_BusinessName, Line 106 A clause of the query contained only ignored words.
This is a classic, the CONTAINSTABLE etc clauses will fail with this
error when the noise word analysis applies its fix to the query clause.
I like to use t-sql stored procs to run the queries, unfortunatly though, with
its total lack of exception handling (unlike oracles pl-sql) its hard to trap such errors elegantly. In the mean time, I have used a table of my own noise words, and so something like:
exec ApplyNoiseWordFix @what, @what output
where @what is what the user was searching for. I then do a simple check to determine if the string is null or blank and apply appropriate business logic.
b) Using CONTAINS or C0NTAINSTABLE
I have a stored proc: sp_SearchOverKeywords @what
if @what => cafes
then this works all well and fine, but
if @what => baby clothes
then the query will crash and burn. You need to simple wrapper the @what in "". This brings about some interesting questions, such as:
is @what meaning "baby AND clothes"
or "baby NEAR clothes"
or "baby OR clothes"
and typically you end up writing an "advanced query" function on your web-site/app or thorough help to allow more experienced users to play.
I apply a range of string manipulation functions to alter the search to the following:
"'baby*' NEAR 'cloth*'"
apparently the inflectional clause can be applied by using ** rather than the INFLECTIONAL clause, but it doesnt seem to work. I have yet to work out how to effectively use it and have the above query flexibility. Any help on this area would be great.
b) I would recommend CONTAINS and CONTAINSTABLE as the key method of quering in virtually all cases. FULLTEXT is ok for sentence type queries or long one-liners, but for keyword searches its poor. I have had a variety of cases where unless you spend the time (like above) with * etc, you can get some strange results back with unusually high rankings.
More to come as I explore Full-text
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"