I work for a client where I inherited a Kentico 8.2 website. As the site had lots of interlinking content and lots of content shared between pages this website was built utilising a lot of SQL queries and views within Kentico.
For the most part it worked absolutely fine with no issues but when it came to searching content it was hard for the client to get the right result. As most of the content existed in linked pages and a lot of the content was separate to the main page meant that the standard searches weren't sufficient. The standard Kentico search didn't search the related data (as the field contained an ID, rather than a searchable text string of the actual related data) and the Crawler type search in this situation produced inconsistent data that the client felt could be much better.
So what we did was produce a custom smart search index in Kentico. I set this up to take four parameters as the input. Firstly the name of the SQL View to produce the search index from, secondly was the field to use as the title field (as this could be different in each view), the third parameter was a comma separated list of fields to contain in the keyword search and finally the fourth field was the field to use as the image (if applicable).
I followed the steps described in the Kentico documentation here https://docs.kentico.com/k10/custom-development/miscellaneous-custom-development-tasks/smart-search-api-and-customization/creating-custom-smart-search-indexes but instead rather than reading in files from the file system I called the SQL View and then returned the items appropriately. I used the fields passed through as parameters to determine the Title Field, the Search field and the Image field. I then looped through all of the fields in the View and passed those through with the column name and the value as the two properties.
This meant that when combining the above along with a custom search transformation meant I had access to the entire field set from within the View to output onto a really nice, functioning search page that did exactly what the client was after and gave us full control over what was included and not included within the search index.
If you are interested in knowing more or would like to see a code example then please do not hesitate to get in touch.
Freelance .NET Developer