sql server - create an indexed view using query results that concatenate row results into 1 column

Question

I am trying to do this: Concatenate many rows into a single text string?

And I want to have the query results join against other tables. So I want to have the csv queries be an indexed view.

I tried the CTE and XML queries to get the csv results and created views using these queries. But SQL Server prevented me from creating an index on these views because CTE and subqueries are not allowed for indexed views.

Are there any other good ways to be able to join a large CSV result set against other tables and still get fast performance? Thanks

Answer 1

Other way is to do materialization by yourself. You create table with required structure and fill it with content of your SELECT. After that you track changes manually and provide actual data in your "cache" table. You can do this by triggers on ALL tables, including in base SELECT (synchronous, but a LOT of pain in complex systems) or by async processing ( Jobs, self-written service, analysis of CDC logs and etc).