SQL Server clustered index on a view

Question

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.

Considering that your tables are only updated once a day, how does filling a table once a day with the results from your query snowball into more changes than a view? — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Jun 22, 2009 at 6:58
post a real example & im sure someone will give you a workable solution. There are options such as computed columns that may fix your problem — Nick Kavadias
– Nick Kavadias, Commented Jun 22, 2009 at 12:08

Dale K · Accepted Answer · 2024-04-02 06:35:07Z

3

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

edited Apr 2, 2024 at 6:35

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jun 22, 2009 at 8:21

Stuart

12.3k6 gold badges36 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

andrej351 Over a year ago

I think your right and thanks to everyone else who posted a suggestion. The system is a complement to a large, old CMS which fills in for some specific business needs. Im essentially massaging their poorly structured data to more acurately model the entities they're interested in.

Jeff Meatball Yang · Accepted Answer · 2009-06-22 07:47:12Z

1

The short answer is that a clustered index cannot be created, due to the reasons you mention.

When you ask for a way to cache the results of the complicated query, the only other object that SQL Server provides (and will solve your issue) is a table.

If automation is a problem, you should consider creating the view, but only using it as a way to insert into a table, such that you can do a truncate/insert into the table (selecting from the view) immediately after the bulk insert.

If you use SSIS (SQL Server Integration Services) this is a relatively trivial thing to add.

answered Jun 22, 2009 at 7:47

Jeff Meatball Yang

39.2k27 gold badges94 silver badges125 bronze badges

Comments

Justin · Accepted Answer · 2009-06-22 08:13:21Z

1

As far as I'm aware, when compiling execution plans SQL Server essentially copies and pastes the definition of the view into the query its compiling - as long as you are able to add indexes to the underlying tables it should be possible to get good performance from the query.

answered Jun 22, 2009 at 8:13

Justin

87.3k49 gold badges231 silver badges374 bronze badges

3 Comments

Lieven Keersmaekers Over a year ago

I don't see any reason to downvote this. With the information provided in the question, there is no way of knowing that the underlying tables are properly indexed or not.

andrej351 Over a year ago

there is an additional issue of an ORM (Entity Framework) sitting on top of all this which adds more overhead. The query is made up of 3 left outer joins and 5 nested queries so if i get time, ill swap the table names out and post it

Justin Over a year ago

If you also post an exectuion plan then the chances are someone here will be able to point out and bottleknecks. Also you might want to try putting the query through the index tuning wizard (if you haven't already done so)

cjk · Accepted Answer · 2009-06-22 08:18:58Z

1

What you are building sounds like a data warehouse, therefore your best option is to manipulate the data once it is in the system. You can build new tables of denormalised (or however else you are modifying it) and index them to allow quick querying.

You can then build views on top of these tables if you need to.

answered Jun 22, 2009 at 8:18

cjk

46.6k9 gold badges83 silver badges113 bronze badges

2 Comments

andrej351 Over a year ago

Thanks for your advice as well, see comment below.

Stuart Over a year ago

A data warehouse might be worth investing in long term, maybe use my answer to get the short term fix, consider if a warehouse can help long term

SQLMenace · Accepted Answer · 2009-06-22 13:13:00Z

1

when using aggregates inside a indexed view you need to use COUNT_BIG() instead of COUNT() otherwise the view won't be created

Also if you are not on Enterprise Edition you need to provide the NOEXPAND hint otherwise the optimizer will not use the view

SELECT *
FROM YourView WITH(NOEXPAND)
WHERE ....

Maybe you don't need a view but you just don't have th correct indexes on the tables, can you post the DDL of the tables (including indexes and constraints)

answered Jun 22, 2009 at 13:13

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Comments

Mark Wood · Accepted Answer · 2009-06-22 08:23:20Z

0

I had the same problem and ended up putting the sub queries in clustered index views themselves.

answered Jun 22, 2009 at 8:23

Mark Wood

Collectives™ on Stack Overflow

SQL Server clustered index on a view

6 Answers 6

1 Comment

Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related