In SQL Server, the indexed view is a hellscape of limitations. But I need one. I have a formatter SQLCLR function that creates a prettified version of a domain key - users want to be able to search on substrings of that prettified version. So I need a full-text index on a persisted calculated column or a materialized view.
However, the formatter depends on data stored in multiple tables.
So this works fine for a view, but not for a persisted computed column because those can't query from multiple tables.
My SQLCLR method is precise and deterministic, so it should be eligible for use in indexed views, but indexed views can't have SQLCLR in their index keys.
I could re-implement my formatter using the T-SQL
FORMAT function... but
FORMAT is also incompatible with indexed views.
Can indexed views do anything? Ever?
I'm open to the "roll your own materialized view with triggers" approach if anybody can refer me to a good document on best practices for doing that. Last time I tried, it spiraled out of control and there was tremendous duplication between the code for insert and update and the initialization.
Is there some way I'm missing to get performant text searches on calculated data on a few million rows, without using a materialized/indexed view or a persisted calculated column?
The formatter does not perform data access. However, the data I need to feed into the formatter for it to be useful will be coming from multiple tables (with a nice regular join), hence I can't solve this problem with persisted computed columns. I can't key the SQLCLR column, so I can't use it in a fulltext index.
I thought this would be simple. Persisted columns and indexed views are designed to perform calculations on write, and to properly implement the observer pattern so that changes to their dependencies are reflected on the calculated value.