NEAR syntax in Full-Text Search

In tuning our search engine which is running against SQL 2005, I'm trying to understand the "near" operator in my CONTAINSTABLE query. I'm doing a query like the following:

select * FROM CONTAINSTABLE(Catalog, *,
'FORMSOF(INFLECTIONAL,"class") OR
FORMSOF(INFLECTIONAL,"calendar") OR
("class" near "calendar")', 1000)

Entries that have class and calendar directly next to eachother are being ranked higher, as would be expected. But entries that have Class <word> calendar, are being ranked the exact same as they would be with just the two Inflectional ORs and no near syntax.

I thought the near syntax was supposed to be useful up to 50 words?

The near syntax is useful up to 50 words means if the two words are more than 50 words apart, the ranking score (of that OR clause) will be 0.

Ranking is rather complicated. In your example, the ranking will be affected by all 3 OR clauses. The ranking also depends on other factors, like document length, average document length of the corpus, frequency of the terms in the document and across corpus. So, word distance in the near clause may or may not be visible in your final ranking score.

NEAR syntax in Full-Text Search

In tuning our search engine which is running against SQL 2005, I'm trying to understand the "near" operator in my CONTAINSTABLE query. I'm doing a query like the following:

select * FROM CONTAINSTABLE(Catalog, *,
'FORMSOF(INFLECTIONAL,"class") OR
FORMSOF(INFLECTIONAL,"calendar") OR
("class" near "calendar")', 1000)

Entries that have class and calendar directly next to eachother are being ranked higher, as would be expected. But entries that have Class <word> calendar, are being ranked the exact same as they would be with just the two Inflectional ORs and no near syntax.

I thought the near syntax was supposed to be useful up to 50 words?

The near syntax is useful up to 50 words means if the two words are more than 50 words apart, the ranking score (of that OR clause) will be 0.

Ranking is rather complicated. In your example, the ranking will be affected by all 3 OR clauses. The ranking also depends on other factors, like document length, average document length of the corpus, frequency of the terms in the document and across corpus. So, word distance in the near clause may or may not be visible in your final ranking score.

Near Proximity Term Questions

Just learning full-text searching in SQL Server 2005 and have questions about the proximity term "near".

1. How near is near? Measured in characters, words, or whatever?
2. How do you know? Is this documented? Can't find it anywhere.
3. Can it be adjusted? at design time? at runtime?

I have used a program called Sonar which has powerful proximity options that allow the user to specify proximity in terms of words at runtime. Would like to be able to do that but can't find much on "near" in the documentation other than it seems to relative, provides for left and right nearness, and allows for chaining of multiple search terms.

Hi,

1. Near is mesured in words (depends on wordbreaker).

2. Please refer to a previous post in MS newsgroups : http://groups.google.com/group/microsoft.public.sqlserver.fulltext/browse_frm/thread/d2a3d0646ee6e68d/61db754db51cc5af?lnk=st&q=Near+keyword+in+Full+Text+Search+SQL+Server+2005&rnum=1&hl=en#61db754db51cc5af

3. NEAR ranking formulas are "by design"

Jean-Pierre Riehl

|||

Hi Jean-Pierre,

Thanks for the very helpful info and link. A follow-up question if you don’t mind.

Since the size of the “Near” proximity parameter seems to be determined by the wordbreaker used by Sql Server's Full Text Search Engine (with a default value of 50 words), can the wordbreaker be altered (with a sql statement or otherwise) to increase or decrease the proximity of “Near” (i.e., make it more or less than 50 words)?