DataFlex – SQL Search

Lesson 2 – Using the freetexttable function

The results in the previous lesson didn’t get sorted on relevance, they were just sorted randomly. You probably want the most relevant result on top, especially when using multiple keywords.

The FREETEXT function only gives true or false, but to search on relevance you also need a relevance rating so then you can use the FREETEXTTABLE function. The FREETEXTTABLE function returns a table. This table has a key which is your primary key of the record that is being found. It also returns a rank which is just the relevance rating. So a number, and how higher the number the more relevant the result is. Use this in the ‘FROM’ class of your query and join in the actual data to show results that make sense. In this lesson we’re going to look at a sample query that shows this.

Back in the SQL management studio you can see the sample query
Select everything from the FREETEXTTABLE and execute this query
Executing this query will provide you with a key which is the primary key of the table that it found, so the track table and the relevance rating/ranking.
The first hit has a high ranking at 142, while the other hits have smaller rankings, so they are less accurate.
But this information isn’t really usable yet, so what you would normally do is to join in the data that you actually want to show.
Do this by using a left join, where you tell it you want every record that’s in the result set, and that we want to join in the actual record with the data. Execute it again.
Now you see the TrackId, the name, the composer and the album. All the information you need and also the search ranking.
This is how you can show your results properly in the application.

SQL Search

DataFlex courses

Information

DataFlex – SQL Search

Lesson 2 – Using the freetexttable function