In this course you will learn how to use SQL full text search and embedded SQL. Before following this course we recommend to watch ‘Building your own search engine’ first, because we will extend that knowledge by using SQL full text catalogs and embedded SQL to improve the results.
For this sample we will use the Chinook database, which has artists, albums and tracks.
SQL Full text search is a separate service that runs next to your database server and it builds up a big catalogue of all the keywords that are used within your content.
It is available on all MS SQL versions and is not always installed by default but you can use it on all different versions.
Create a catalogue by selecting the fields that you want to have indexed. You can index any type of field, text fields, binary fields or …. Fields. It provides better performance on large datasets, it has language enhancements, relevance rating and it’s highly configurable.
You can schedule it to populate overnight, so it won’t take any performance during the day. The simplest way of using it is using the FREETEXTFILTER, the FREETEXTFILTER can be used in the work class of your SQL query to filter the results using keywords that it looks for in the catalogue. The columns need to be part of the catalogue or it will provide an error. In the first lesson, you will learn how to use this within a SQL query.