Problem with Full-Text Indexing in SQL Server 2008

Dear All,
Hi. I have a table in SQL Server 2008 database with compatibility level set to "80" (SQL SERVER 2000). This table is a part of a full-text index containing rules only for this table. The full-text index has five columns of the table qualified as "Eligible Columns".  In order to allow the insertion of a lengthy data for one field say "field2" Varchar(3000) , I increased the size to Varchar(3500). After that, I run the full-text index.  But the search results are not working properly.  

For example :

1.a. Contains(SearchPhrase, '"P." And "K." And "Mathis"') -- (Camel Case) Fetches results for Camel Cases.
1.b. Contains(SearchPhrase, '"p." And "k." And "mathis"') -- (small case) does not fetch result

2.a. where contains(SearchString, '"Institute of advanced study in education"') -- fetches results
2.b. where Contains(SearchString, '"Institute" And "of" And "Advanced" And "Study" And "in" And "Education"') -- does not fetch result

Any insights are appreciated.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Is there by any chance you can Case Sensitive collation defined for your database?
May I know what is the collation of your database and the table columns involved.
ste5anSenior DeveloperCommented:
First of all: Can you rephrase your question? How do you search what exactly? What role are playing those "rules"?

Then: FULLTEXT queries are case-insensitive.

Without testing:

1) The dot after is handled as a word break, the remaining single letter is normally a noise word and removed. This is controlled by the noise settings according to your language. See Configure and Manage Stopwords and Stoplists for Full-Text Search. And as you're running in SQL 2000 mode, take a look at the noise word files in FTDATA.

2) "and" and "in" are normally stop-words and not part of the index. Thus test without them in your b) case.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Then: FULLTEXT queries are case-insensitive.

Yes, ste5an that's correct..
But I doubt that the issue reported by author has something to do with incorrect collation settings or something related to that..
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ankurdkAuthor Commented:
Dear @Raja Jegan R, Dear@Ste5an, thanks for your replies.

@Raja Jegan R: Yes, I forgot to mention the collation. Its SQL_Latin1_General_CP1_CI_AS so its case insensitive.  My apologies for skipping it earlier. It goes for the table as well.  

@Ste5an:  I am making these searches through an in-house customized application.  By "rules" I was trying to refer to the full-text rules by the default nature of it.  When the search is made with exact phrase (ref. ex. 1.a) i.e. with a capital P and a Capital K, results are shown but same is not the case when I use small letters.

It is significant to mention that prior to length changes, the same searches resulted in output.

PS: In my original question "SearchPhrase" and "SearchString" both refer to column names.

lcohanDatabase AnalystCommented:
I see you mentioned that "... I run the full-text index" after you altered the column however did you try a FULL REBUILD your FULL TEXT catalog after the underlying table column data type change? Alternatively you could try CONTAINSTABLE instead of CONTAINS as this should return the rank as well and ultimately even try the FREETEXT instead however the FREETEXT is searches for the meaning of words not just the values it may return more data than wanted.

Just currious as this is running in old compatibility mode if you try to disable the stoplist like


and also maybe try rebuild your catalog with WITH ACCENT_SENSITIVITY = OFF to see it this helps. All these can be undone of course as I have the feeling these changes are in the production environment already but ideally this should been done in a DEV/QA environment first.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Please test directly in SSMS.

I'm not sure whether in your situation the "old" noise words lists are used or the "newer" stop words. Imho the problem is caused by single letters being noise (stop words) by default.

And finally: FULLTEXT search is for naturally spoken language.
ankurdkAuthor Commented:
@lcohan thanks for a comprehensive reply. I shall try out the suggestions mentioned by you and then get back; hopefully with a positive reply.

Thanks again.
ankurdkAuthor Commented:
Dear @Ste5an,
Thanks for your reply.  I shall try out things suggested and get back. But, I am still wondering how come things were working prior to length change and everything went haywire by increasing the length despite re-running the full-text time and again ?

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> But, I am still wondering how come things were working prior to length change and everything went haywire by increasing the length despite re-running the full-text time and again ?

Missed noticing this column length change before..
If this was done, kindly let us know whether you have rebuilt the Full text catalog or not.
if not, then try to do that once to see whether it helps or not.

or else, try dropping the Full text index and recreate it to see whether it helps out or not.
As ste5an mentioned about the noise words, you can also try to exclude the noise words like "and" and "." from your search by following the below steps and rebuild the full text catalog once.
ankurdkAuthor Commented:
@Icohan ALTER FULLTEXT INDEX on my_table SET STOPLIST=OFF; worked for me! thanks.
@ste5an and @Raja Jegan R thanks for your contributions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.