Prev: Sql Record number
Next: bin packing
From: Hemant on 20 Oct 2009 08:17
hi,

Freetext not working properly.
For example:
I am writing
select * from table1 where productname like '%meals for%'
this query give me 5 rows
but below query didn't give me any row.
select * from table1 where FREETEXT (table1 .productname, meals for)
why?
It's working for other product name .

thanks
Hemant


From: Russell Fields on 20 Oct 2009 09:23
Hement,

Your second query has a syntax error, so I assume that is just a mistype.

Option 1:
select * from table1 where FREETEXT (table1.productname, 'meals for')
Because the two words are ORed, this will find:
Meals that are for children.
Meals that are no good.
For whatever reason.

Option 2:
select * from table1 where FREETEXT (table1.productname, '"meals for"')
This is a phrase and it will not find the above rows, but will find:
Meals for elderly.

However, because 'for' is in the standard noise word list, it will not be
indexed unless you removed it from your noise word list before building your
full-text index.

The noise word list for your full-text index language is found somewhere
like: C:\MSSQL\FTDATA. (US English is noiseENU.txt.) If you want 'for'
and other words like it to be indexed, you will need to edit your noise word
list to remove those words, then rebuild the index. Note that the noise
word list cannot be empty but must contain at least 1 blank space. (I
usually put one word in the list, such as
'ThisNoiseWordListIsIntentionallyShort'.)

Finally, CONTAINS provides more precision that FREETEXT, if that turns out
to be important to you.

RLF


"Hemant" <Hemant(a)nomail.com> wrote in message
news:%23cHGW7XUKHA.1280(a)TK2MSFTNGP04.phx.gbl...
> hi,
>
> Freetext not working properly.
> For example:
> I am writing
> select * from table1 where productname like '%meals for%'
> this query give me 5 rows
> but below query didn't give me any row.
> select * from table1 where FREETEXT (table1 .productname, meals for)
> why?
> It's working for other product name .
>
> thanks
> Hemant
>

From: Hemant on 21 Oct 2009 02:21
thanks ,
You give me a lot of information about freetext .
my problem solved after rebuilding my catalog and restarting the ms serach
service .
after repopulating it is working fine.
but why i have to repopulate it ?
or i should create a schedule ?
suggest me.

thanks,
hemant
"Russell Fields" <russellfields(a)nomail.com> wrote in message
news:euw8$hYUKHA.4592(a)TK2MSFTNGP06.phx.gbl...
> Hement,
>
> Your second query has a syntax error, so I assume that is just a mistype.
>
> Option 1:
> select * from table1 where FREETEXT (table1.productname, 'meals for')
> Because the two words are ORed, this will find:
> Meals that are for children.
> Meals that are no good.
> For whatever reason.
>
> Option 2:
> select * from table1 where FREETEXT (table1.productname, '"meals for"')
> This is a phrase and it will not find the above rows, but will find:
> Meals for elderly.
>
> However, because 'for' is in the standard noise word list, it will not be
> indexed unless you removed it from your noise word list before building
> your full-text index.
>
> The noise word list for your full-text index language is found somewhere
> like: C:\MSSQL\FTDATA. (US English is noiseENU.txt.) If you want 'for'
> and other words like it to be indexed, you will need to edit your noise
> word list to remove those words, then rebuild the index. Note that the
> noise word list cannot be empty but must contain at least 1 blank space.
> (I usually put one word in the list, such as
> 'ThisNoiseWordListIsIntentionallyShort'.)
>
> Finally, CONTAINS provides more precision that FREETEXT, if that turns out
> to be important to you.
>
> RLF
>
>
> "Hemant" <Hemant(a)nomail.com> wrote in message
> news:%23cHGW7XUKHA.1280(a)TK2MSFTNGP04.phx.gbl...
>> hi,
>>
>> Freetext not working properly.
>> For example:
>> I am writing
>> select * from table1 where productname like '%meals for%'
>> this query give me 5 rows
>> but below query didn't give me any row.
>> select * from table1 where FREETEXT (table1 .productname, meals for)
>> why?
>> It's working for other product name .
>>
>> thanks
>> Hemant
>>
>


From: Russell Fields on 21 Oct 2009 12:04
Hemant,

It all depends on how you defined your fulltext indexes and how they are
maintained. Most important for your question is how is CHANGE_TRACKING set?
Here is an example script:

CREATE FULLTEXT INDEX ON [dbo].[ProductSearchText]([SearchText])
KEY INDEX [PK_ProductSearchText] ON [Cat_Product_FTQueries]
WITH CHANGE_TRACKING AUTO

Change_Tracking set to AUTO means that as rows are updated, the FullText
engine will automatically start updating the indexes. This is asynchronous,
so it happens soon, but not immediately. If this is set to OFF or MANUAL,
you have to run a process that will do an ALTER to start the rebuild of the
index.

Best idea is CHANGE_TRACKING AUTO. (However, if your data is totally
dropped and recreated by some kill and fill process, leave CHANGE_TRACKING
OFF and do a specific population of the index through a SQL Agent job.)

To read the descriptions of the commands and their consequences read:
CREATE FULLTEXT INDEX
http://msdn.microsoft.com/en-us/library/ms187317(SQL.90).aspx
ALTER FULLTEXT INDEX
http://msdn.microsoft.com/en-us/library/ms188359(SQL.90).aspx

This property can also be set through the SSMS interface. Right click on
the table with the fulltext index, select "Full-Text Index" then
"Properties" and set the Change Tracking value to Auto. Do one more
repopulation to get everything set up for the future.

All the best,
RLF


"Hemant" <Hemant(a)nomail.com> wrote in message
news:es0XDZhUKHA.4704(a)TK2MSFTNGP06.phx.gbl...
> thanks ,
> You give me a lot of information about freetext .
> my problem solved after rebuilding my catalog and restarting the ms serach
> service .
> after repopulating it is working fine.
> but why i have to repopulate it ?
> or i should create a schedule ?
> suggest me.
>
> thanks,
> hemant
> "Russell Fields" <russellfields(a)nomail.com> wrote in message
> news:euw8$hYUKHA.4592(a)TK2MSFTNGP06.phx.gbl...
>> Hement,
>>
>> Your second query has a syntax error, so I assume that is just a mistype.
>>
>> Option 1:
>> select * from table1 where FREETEXT (table1.productname, 'meals for')
>> Because the two words are ORed, this will find:
>> Meals that are for children.
>> Meals that are no good.
>> For whatever reason.
>>
>> Option 2:
>> select * from table1 where FREETEXT (table1.productname, '"meals for"')
>> This is a phrase and it will not find the above rows, but will find:
>> Meals for elderly.
>>
>> However, because 'for' is in the standard noise word list, it will not be
>> indexed unless you removed it from your noise word list before building
>> your full-text index.
>>
>> The noise word list for your full-text index language is found somewhere
>> like: C:\MSSQL\FTDATA. (US English is noiseENU.txt.) If you want 'for'
>> and other words like it to be indexed, you will need to edit your noise
>> word list to remove those words, then rebuild the index. Note that the
>> noise word list cannot be empty but must contain at least 1 blank space.
>> (I usually put one word in the list, such as
>> 'ThisNoiseWordListIsIntentionallyShort'.)
>>
>> Finally, CONTAINS provides more precision that FREETEXT, if that turns
>> out to be important to you.
>>
>> RLF
>>
>>
>> "Hemant" <Hemant(a)nomail.com> wrote in message
>> news:%23cHGW7XUKHA.1280(a)TK2MSFTNGP04.phx.gbl...
>>> hi,
>>>
>>> Freetext not working properly.
>>> For example:
>>> I am writing
>>> select * from table1 where productname like '%meals for%'
>>> this query give me 5 rows
>>> but below query didn't give me any row.
>>> select * from table1 where FREETEXT (table1 .productname, meals for)
>>> why?
>>> It's working for other product name .
>>>
>>> thanks
>>> Hemant
>>>
>>
>
>

 | 
Pages: 1
Prev: Sql Record number
Next: bin packing