Full Text Search Ranking (SQL Server)

Question

Over the past two hours, I have been busy with all sorts of SQL Server full-text searches. However, I still can’t understand how the rating works. I came across several examples that really confuse me as to how they are taller than others. for example

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

I run this query (well, almost .. I reprinted 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 lines 1 and 2 are higher than line 3, and why line 3 is evaluated the same as line 4. Line 2 should have the highest rank, seeing that the search line also matches the first and last name as the date of birth.

If I change OR to AND, I will not get any results.

Answer 1

I found that the AND and OR clauses do not apply to columns. Create an indexed view that combines columns and you get better results. Take a look at my past questions and you will find information that matches your scenario.

I also found that I better not add the '*'. I thought he would have more matches, but he usually returns worse results (especially for long words). As an intermediate level, you can only add * to longer words.

The example you give is definitely strange.

Answer 2

This is not completely equivalent, but maybe this question I asked ( How-To: Search Results Ranking ) might be helpful?

Answer 3

What happens if you delete the DoB criteria?

MS Full-text search is really a really black box that is hard to understand and tune in. You pretty much take it as it is, unlike Lucene, it's great for tuning

Answer 4

Thanks guys.

Frank, you were right that AND and OR did not go in columns, that was what I did not notice at the beginning.

To get the best results, I had to combine all 5 columns into 1 column in the view. Then search in one column. This gave me the exact results that I wanted without any additional services.

My actual search string after converting it turned out to be "Word1 *" AND "Word2 *"

Using the% sign still doesn't do what msdn said it should do. Meaning, if I searched for the word “Josh” and it was changed to “Josh%” when I searched, then “Joshua” will not be found. Pretty stupid, however, with Josh, then Joshua will be found.