I originally posted this on allthingswilson.com almost 2 years ago. However, I find it to be very helpful so I am reposting it here. I realize that I am not blazing any new trails here, but hopefully someone will find this useful.

A client of our’s recently approached us with a simple request, to create a business search which would search keywords on the business’ name and description in our SQL Server database. This was simple enough to achieve, by creating a FULLTEXT index on the table with the appropriate fields ( title, description ) and executing the following query:

   SELECT Members.*, SEARCH_TBL.RANK AS rank
     FROM   Members INNER JOIN
           FREETEXTTABLE(Members, *, ‘breakfast’) SEARCH_TBL
               ON Members.memberID = SEARCH_TBL.[KEY]
    ORDER BY rank DESC

Which yields the following results for the keyword ‘breakfast’:

id name description rank
23 Holiday Inn – Downtown <Long Text> 276
71 IHOP <Long Text> 220
2 Days Inn – North <Long Text> 155
43 Best Breakfast Buffet in Town <Long Text> 146

As we can see, these results are unsatisfactory as two businesses appear above ones that should be first. This is due to the fact that these hotels keyword-loaded their description by boasting about their, no doubt, wonderful continental breakfast. Well, we have been there and we have been to the BBBIT and IHOP, and we realize that it is not even a contest as to which one is better. So we would like for the genuine greasy spoon breakfast joints to appear ‘ranked’ above the giant monster mega hotel chains.

Unfortunately, SQL Server has no native way of ‘prioritizing’ columns in a FULLTEXT search clause, that I know of (unlike Karl Hungus, I am not an expert). However, this needed to be done, and I did not want to go messing with the code, so I set about trying to come up with a way to do it in my stored procedure. I suck at T-SQL, but I am pretty good at joining tables, it’s simpler for my simple mind.

Here is the new query:

   SELECT Members.*,
           NAME_SRCH.RANK AS rank1, SRCH.RANK AS rank2
      FREETEXTTABLE(Members, name, ‘breakfast’) NAME_SRCH ON
        Members.memberID = NAME_SRCH.[KEY] LEFT OUTER JOIN
      FREETEXTTABLE(Members, *, ‘breakfast’) SRCH ON
        Members.memberID = SRCH.[KEY]
   ORDER BY rank1 DESC, rank2 DESC

This query yielded the following result set:

id name description rank1 rank2
43 Best Breakfast Buffet in Town <Long Text> 265 146
2 Cozy Bed & Breakfast <Long Text> 265 120
23 Holiday Inn – Downtown <Long Text> <NULL> 276
71 IHOP <Long Text> <NULL> 220

Now you see we have much better results, the Holiday Inn hung in there but we also introduced a new business, Cozy Bed & Breakfast, which I think we can agree is a desirable result for a breakfast search. IHOP still came in after the hotel, because they do not have breakfast in their name. But alas, as we all know by now, outside of Google, no search is perfect.

Tags: ,