In part one, it is shown how to enable basic Full-Text search options on a table in SQL Server. But often our required data is not available in a table that may be available across multiple tables. In this case, we can make a VIEW and then apply the Full-Text search option on it.
In this part, we will try to show how to make VIEW and enable the Full-Text search option on it. In this example we need to search by name and address of customer and required data is available in Person table & Address table. We may not want to search separately, we want at one go.
1) First, we will check if VIEW already exists or not. If not, then we will create a new one with SCHEMABINDING.
2) Now need to create a unique clustered index on this VIEW
While running the above query, the system shows an error message. Because we need to create a FULLTEXT INDEX on our VIEW with KEY INDEX.
4) Create a FULLTEXT INDEX on VIEW CustomerInfoIndexView
5) Test again and see the result
6) Now test again with both FREETEXT & CONTAINS and see the difference
To know why this result is showing see part-1
To get a tailored application hire Developers from us
7) To get better benefits we can combine both FREETEXT and CONTAINS. But Suffix searches are not supported in CONTAINS, they only support Prefix.
