Ranking of Full Text Search (SQL Server)

Question

For the last couple hours I have been messing with all sorts of different variations of SQL Server full text search. However I am still unable to figure out how the ranking works. I have come across a couple examples that really confuse me as to how they rank higher then others. For example

I have a table with 5 cols + more that are not indexed. All are nvarchar fields.

I am running this query (Well almost.. I retyped with different names)

SET @SearchString = REPLACE(@Name, ' ', '*" OR "') --Splits words with an OR between
SET @SearchString = '"'+@SearchString+'*"'
print @SearchString;

SELECT ms.ID, ms.Lastname, ms.DateOfBirth, ms.Aka, ms.Key_TBL.RANK, ms.MiddleName, ms.Firstname
FROM View_MemberSearch as ms
INNER JOIN CONTAINSTABLE(View_MemberSearch, (ms.LastName, ms.Firstname, ms.MiddleName, ms.Aka, ms.DateOfBirth), @SearchString) AS KEY_TBL
    ON ms.ID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 0
ORDER BY KEY_TBL.RANK DESC;

Thus if I search for 11/05/1964 JOHN JACKSON I would get "11/05/1964" OR "JOHN*" OR "JACKSON*" and these results:

ID -- First Name -- Middle Name -- Last Name -- AKA -- Date of Birth -- SQL Server RANK
----------------------------------------------------------------------------------
1  |  DAVE       |  JOHN        |  MATHIS     | NULL | 11/23/1965    |  192
2  |  MARK       |  JACKSON     |  GREEN      | NULL | 05/29/1998    |  192
3  |  JOHN       |  NULL        |  JACKSON    | NULL | 11/05/1964    |  176
4  |  JOE        |  NULL        |  JACKSON    | NULL | 10/04/1994    |  176

So finally my question. I don't see how row 1 and 2 are ranked above row 3 and why row 3 is ranked the same as row 4. Row 2 should have the highest rank by far seeing as the search string matches the First name and Last Name as well as the Date of birth.

If I change the OR to AND I don't get any results.

Solution 1

I've found AND and OR clauses don't apply across columns. Create an indexed view that merges the columns and you'll get better results. Look at my past questions and you'll find information that suites your scenario.

I also have found I'm better off not appending a '*'. I thought it'd turn up more matches, but it tended to return worse results (particularly for long words). As a middle ground you might only append a * to longer words.

The example case you give is definately weird.

Solution 2

It's not entirely equivalent, but perhaps this question I asked (How-to: Ranking Search Results) could be of assistance?

Solution 3

Thank you guys.

Frank you were correct that AND and OR do not go across columns this was something I did not notice at first.

To get the best results I had to merge all 5 columns into 1 column in a view. Then search on that single column. Doing so gave me the exact results I wanted without any extras.

My actual search string after converting it ended up being "Word1*" AND "Word2*"

Using the % sign still did not do what msdn said it should do. Meaning if I searched for the word josh and it got changed into "Josh%" when I searched then "Joshua" would not be found. Pretty dumb however with "Josh*" then joshua would be found.

Solution 4

What happens if you remove the DoB criteria?

MS Full-Text search is really really a black box that's hard to understand and customize You pretty much take it AS IS, unlike Lucene is great for customization

View more solutions

Comments

[zsharp]

how to merge the columns and use exactly? i have same issue.

[user636525]

Is it better to have a computed column and have a full text index on that column than have to search on separate columns ?