...
Option 1:
Having a column in the table itself simplifies a lot the queries since they can be translated directly (e.g. now complicated JOIN or IN clauses).
We could treat it as an eventual consistent operation if we want to speed up the transactions but I would not start with such an implementation for now considering that the amount of expected data will be limited. If we have to support very big tables (>100K rows) we should consider using a dedicated separate search index.
Note that since the virtual SEARCH_CONTENT column is not part of the column model of a synapse table, users cannot select or update its value.
Option 2 and 3:
This might the most flexible and simple solution (e.g. we do not have to do special work in the backend), users could decide to apply some pre-processing to the data that is indexed (e.g. stemming).
This options allow the users to “tweak” the search results, e.g. adding synonyms, removing unwanted tokens etc
Since the column is part of the model it is returned in the results for a
select *.This would potentially work automatically even with views