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.

Answer 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.

Answer 2

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