Problem with Full-Text Indexing in SQL Server 2008

ankurdk
ankurdk used Ask the Experts™
on
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.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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 Developer

Commented:
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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 VB.net 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.

Regards.
Database Analyst
Commented:
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

ALTER FULLTEXT INDEX on my_table SET STOPLIST=OFF;

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.
ste5anSenior Developer

Commented:
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.

Author

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.

Author

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 ?

Thanks.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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.
http://www.dbafire.com/2017/11/04/sql-server-full-text-search-problems/

Author

Commented:
@Icohan ALTER FULLTEXT INDEX on my_table SET STOPLIST=OFF; worked for me! thanks.
@ste5an and @Raja Jegan R thanks for your contributions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial