Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do stopwords function in SQL 2008?

Status
Not open for further replies.

AndyInNC

Programmer
Sep 22, 2008
76
US
We are implementing a full-text search in our database and the goal is to be as search engine-like as possible for the user. Unfortunately, it has been difficult finding clearly written information about some of these things involved in the full-text search.

Right now, I am trying to understand the effect of stopwords on the search.

Here is my search phrase: "children washing their hands"

and here is my SQL statement:
Code:
[blue]SELECT[/blue]    TOP 200 j.iJustificationID, j.sJustifications, ct.RANK 
[blue]FROM[/blue]      JUSTIFICATIONS j 
          [blue]INNER JOIN[/blue] CONTAINSTABLE (JUSTIFICATIONS, sJustifications, [red]
                'FORMSOF(INFLECTIONAL, children) 
                AND FORMSOF(INFLECTIONAL, washing)
                AND FORMSOF(INFLECTIONAL, their)
                AND FORMSOF(INFLECTIONAL, hands)'[/red]) AS ct
                  [blue]ON[/blue] j.iJustificationID = ct.[KEY]
[blue]WHERE[/blue]     sJustifications [blue]LIKE[/blue] [red]'%Children washing their hands%'[/red]

The goal of this statement is to return an exact match of the search phrase. (The whole table is supposed to be filtered with the CONTAINSTABLE JOIN with further refinement by the WHERE clause.) Now the phrase exists in the data, but the their stop word prevents any results from returning. I get nothing.

However, if I adjust the table properties to not use a stoplist, I get results.

Does "stop word" mean it stops the query? 'Cuz it sure seems that way! Maybe I am better off not using stoplists at all or I can remove the FORMSOF phrase if there is a stopword. Any thoughts or links?
 
When you have the stop word list applied to the full text index it excludes the word "their" from the index meaning that it isn't possible to match against the containstable predicate when looking for that word. You'll want to see what the list of words are and exclude the stop words from the query.

Also you should look into the NEAR syntax and remove the WHERE clause. That WHERE clause will not scale as you can't use an index with a wildcard at the beginning of the value being matched.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top