Search code examples
sqlsql-servert-sqlviewclustered-index

SQL Server clustered index on a view

I'm trying to create a view out of quite a complex select query and it won't let me put a clustered index on it because I have to use subqueries and some aggregate functions.

I have to get a clustered index on it otherwise the queries that use the view will take forever. Apparently SQL Server will only store the result set if you meet a specific criteria.

The base tables for the view are read-only and and only get updated by a bulk import once a day. I can't see why the results can't be cached.

Does anyone know of any way to get SQL Server to cache the results of a view so they can in turn be queried later? I don't really want to create another table because that would snowball into a bunch of changes all over the place.

Thanks in advance.

Solution

  • I think the answer you are looking for is:

    • Don't use a view to do this.
    • Use a table with the fields corresponding to the returned fields form the SQL query.
    • Automate the query to populate this table