Caching derived dataset automatically in SQL Server I have a view that combines multiple tables via UNION ALL, and handles some basic errors with the data. Due to the way that the errors are handled, the view is a bit slower than I would hope. Is there any way to cache the output of the view, and dynamically update it whenever any of the underlying tables are updated? 

A few quirks of my system:


The database only sees updates once a day, during off-hours. So a resource-intensive solution is fine, as long as the resources are consumed during these off-hours.
Due to the way the system is set up, I would prefer to not clean up the data while loading it, and would rather leave the underlying tables untouched.
I have appropriate supporting indexes set up
The tables all contain distinct records with no overlap, so I need to pull them all in


Some options I've considered:


An indexed view won't work due to the view relying on the UNION operator. 
I haven't found any way to cache the output of a TVF. 
I could create a cache table and manually load it any time the underlying table if modified, but this feels like a hacky workaround. I think I'd also have to create a SQL Agent Job for this, since setting it as a trigger would involve refreshing the table millions of times each evening while the table is overwritten.
I could live with the poor performance, but this would negatively impact end-users.


Here's a simplified example of the view:

CREATE OR ALTER VIEW AllNamesAndAccountNumbers AS 
SELECT
    x.AccountNumber
    , COALESCE(x.FullName, n.FullName) AS FullName
FROM (
    SELECT
        AccountNumber
        , FullName 
    FROM TableWithNames
    UNION ALL
    SELECT
        AccountNumber
        , NULL AS FullName
    FROM TableMissingNames
) x
LEFT JOIN NameMappings n -- this join is slow due to the table being really huge
ON n.AccountNumber = x.AccountNumber


Oh - if this is an antipattern, and I should be attacking this from a fundamentally different angle, please say so. I'm concerned I'm looking at this wrong.
I could create a cache table and manually load it any time the underlying table if modified, but this feels like a hacky workaround. I think I'd also have to create a SQL Agent Job for this


That's the fallback.  And it's totally normal to run an ETL job after loading staging tables.

You might be able to do something with 2 indexed views instead of one.  And, of course, you can play with indexes and columnstore tables to improve query performance.