ankurdk
asked on
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.
Thanks.
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.
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.
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.
>> 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..
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..
ASKER
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_
@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.
@Raja Jegan R: Yes, I forgot to mention the collation. Its SQL_Latin1_General_CP1_CI_
@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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
@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.
Thanks again.
ASKER
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.
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.
>> 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/
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/
ASKER
@Icohan ALTER FULLTEXT INDEX on my_table SET STOPLIST=OFF; worked for me! thanks.
@ste5an and @Raja Jegan R thanks for your contributions.
@ste5an and @Raja Jegan R thanks for your contributions.
May I know what is the collation of your database and the table columns involved.