Have been told that a search using the freetext predicate is based on the meaning not matches (like contains) of search values. I tested this with the adventure database and used maintain which found records with maintenance. I am not sure how this works. I thought that one would have to set a thesaurus. This does not seem to be the case. How does this work? Is there an inbuilt thesaurus. For stop lists there is the inbuilt system stop list which one can clone and update. How does one extend/modify what freetext matches on without using a thesaurus. From
https://docs.microsoft.com/en-us/sql/t-sql/queries/freetext-transact-sql > Is a predicate used in the Transact-SQL WHERE clause of a Transact-SQL SELECT statement to perform a SQL Server full-text search on full-text indexed columns containing character-based data types. This predicate searches for values that match the meaning and not just the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches: > * Separates the string into individual words based on word boundaries (word-breaking). > * Generates inflectional forms of the words (stemming). > * Identifies a list of expansions or replacements for the terms based on matches in the thesaurus. (???) Ok, I had not created (or updated the empty xml supplied one) and it seemed to do some matching. Not sure how this works. Have had no luck finding out.
The standard xml thesaurus (for en) is empty to my knowledge. Its basically a skeletal file for one to update. I was trying to understand how the FREETEXT predicate matches search terms when the XML thesaurus is not populated. I would think that inflectional forms of words is the key. However I am not sure if this is true. If it is true where are these inflectional forms stored? Can you view them? For example I was using an example for the Adventure database and I used freetext with the term maintain. It matched records containing maintenance which is good. I assume maintain is an inflectional term for maintenance. I have been playing with CONTAINS and that works well. I have been experimenting with FREETEXT to increase my understanding.