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 operator

What is the specific function of NEAR?
I'm indexing across 3 text fields and expect hits for 'white NEAR black' and
only getting a hit when the two words are in the same field, but not if they
are spread across two of the fields.
How NEAR does the other word have to be?
Nearness is reflected in rank. The closer together two words are the higher
the rank everything else being equal.
Nearness is calculated on a per column basis, it cannot look across columns.
FreeText factors nearness into rank (everything else being equal) and can
look across columns. FreeText ignores the near operator however, as it is
calcuated in the rank.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Bill D" <delaneWC-nospam@.mail.slh.wisc.edu> wrote in message
news:e2kX0QboEHA.3488@.TK2MSFTNGP12.phx.gbl...
> What is the specific function of NEAR?
> I'm indexing across 3 text fields and expect hits for 'white NEAR black'
and
> only getting a hit when the two words are in the same field, but not if
they
> are spread across two of the fields.
> How NEAR does the other word have to be?
>
|||Very helpful. I had found that using Freetext instead of Contains was
getting me more results. Thanks Hilary.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Op2bxxboEHA.1576@.TK2MSFTNGP12.phx.gbl...
> Nearness is reflected in rank. The closer together two words are the
higher
> the rank everything else being equal.
> Nearness is calculated on a per column basis, it cannot look across
columns.
> FreeText factors nearness into rank (everything else being equal) and can
> look across columns. FreeText ignores the near operator however, as it is
> calcuated in the rank.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Bill D" <delaneWC-nospam@.mail.slh.wisc.edu> wrote in message
> news:e2kX0QboEHA.3488@.TK2MSFTNGP12.phx.gbl...
> and
> they
>

NEAR operator

Is it possible in any way to control the NEAR operator so that it returns
only records containing the search words within a certain distance - e.g
within 3 words or 5 words or a paragraph etc.?
Apparently the way the NEAR operator works is that it returns all (or almost
all..) the records containing the specified words, and then ranks them based
on the words 'nearness'. The problem with this approach is that if the result
of the search are display ordered not by rank but by some other criteria
using 'NEAR' is exactly the same as using 'AND' - and as a matter of fact
newspaper librarians and journalist always sort the result of a search by
publishing date, not by ranking, so no NEAR operator with SQL full text for
them.
Thank you
- Michele
Michele,
Unfortunately, no. There is no way to control how the NEAR operator
determines "nearness" as it is hard-coded at 50 words and the "definition of
nearness is fixed inside mssearch", and are not user controllable :-(
The following quote (from a Microsoft FTS Developer) was taken from another
thread on this subject related to SQL Server 2005 (Yukon), but also applies
to SQL Server 2000 and proximity (or NEAR) searches and RANK:
"distance between terms for a match
number of matches
document length
etc..
so it is possible for a document with term1 right next to term2 to return a
lower rank than another document with many matches with greater distance
between terms:
eg:
document1 = term1 term2 word word word word word word word word word word
word word word.... word word
document2 = term1 word term1 word term1 word term2 word term1 word term2
word term1 word term2 word term1 word term2 word term1 word term2
document1 may have a lower rank that document2 because it has fewer matches
even though the one match it has is very "near"."
Hopefully this sheds more light on this subject.
Thanks,
John
"Michele Mottini" <Michele Mottini@.discussions.microsoft.com> wrote in
message news:380FD11C-01C0-4AC8-BC8B-82EC0524F6B7@.microsoft.com...
> Is it possible in any way to control the NEAR operator so that it returns
> only records containing the search words within a certain distance - e.g
> within 3 words or 5 words or a paragraph etc.?
> Apparently the way the NEAR operator works is that it returns all (or
almost
> all..) the records containing the specified words, and then ranks them
based
> on the words 'nearness'. The problem with this approach is that if the
result
> of the search are display ordered not by rank but by some other criteria
> using 'NEAR' is exactly the same as using 'AND' - and as a matter of fact
> newspaper librarians and journalist always sort the result of a search by
> publishing date, not by ranking, so no NEAR operator with SQL full text
for
> them.
> Thank you
> - Michele
>