http://sqlandmorewithkruti.blogspot.in/2016/04/all-about-searching-full-text-indexes_7.html
In this blog we shall see its implementation. Before you proceed you need to ensure that this feature is added to your instance and the full-text services are running.
We shall start by creating a full text catalog. If multiple filegroups are available we may specify the one on which it has to be created by the FileGroup clause If multiple filegroups are available we may specify the one on which it has to be created by the FileGroup clause
Through Wizard
Unlike Standard indexes we aren’t giving it any name. As SQL server allows only one Full-Text index per table the naming is taken care by SQL server. The Key Index clause specifies the unique non-null column which is mandatory when create full-text indexes.
When Manual is select the changes are tracked but the indexes aren’t updated unless the ALTER FULLTEXT INDEX … START UPDATE POPULATION is fired. This may be called through a SQL agent job as well. There is one more option which is not commonly used.
It’s the Change_Tracking Off option when the changes are not tracked at all by SQL server. It’s available when creating full-text indexes in static tables.
Here im creating my own StopList. This option is available under Database-> Storage -> Full Text StopList. I shall be building it from the system stop list and adding the word “describes” to StopList.
Now we are ready with the index let’s start the querying
https://msdn.microsoft.com/en-us/library/hh245284.aspx
https://msdn.microsoft.com/en-us/library/cc879306.aspx
So coming back to the same example that we discussed in the previous blog I can now search for all the books that contain the character “Jeremy” in the synopsis as follows
The Contains Predicate will search for the exact words or phrases that I pass. It also searches for proximity words & inflecting forms of the word. If you want more matches in terms of meaning of the words and not just the exact words we use the FreeText Predicate. It also allows for pattern matching with wildcards.
This will result for all those records will also contain the records like new Yorker, new york in any of the columns
