Ranking of Full Text Search (SQL Server)

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.

Answers


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.


Need Your Help

New thread opens new window, how to update text box in new window?

c# wpf multithreading

I recently made a post about this but I think my last question was not well written and I did get a answer but I want to see if there is a simpler solution as I found the last one confusing. This t...

How to assign same signature to functions with different signatures?

c++ function pointers boost functor

In my C++ application, I have 2 threads: (i) main thread, (ii) background thread.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.