SQL Server Performance Forum – Threads Archive

General questions on Views

Does anyone have any comments on the following (from a principle point of view)? 1) Is it bad practise to nest views? e.g. is it likely to consume an inefficient amount of resource?
2) If I have 2 nested queries, then will the optimiser work on the complete query, or does it work in two distinct steps? Also, when the inner query returns its results, will there be any indexes or similar on these results which will then be used by the outer query?
3) Do views have any advantage over Table Functions? (Are views getting to be "Old Hat"?) Any thoughts would be appreciated. Ian

---

Hi ya, I’ve not had any problems with nested views before, in fact in some cases adding nesting has improved things for me. SQL normally expands out the views to their base table representation and then looks at the expanded query for index usage. SQL processes nested queries in one step, it won’t get the results from the deepest nested view first or anything like that table functions are useful, but generally only when the number of results are relatively small. The only way to read a table variable is pretty much a table scan. Functions are also more limited in what they can do by the parameters passed to them, views allow ad-hoc querying and the use of base-table indexes Cheers
Twan

---

Not that it changes conclusions, but just for the record: You can create pk on table variable, you can also make it clustered.

---

Views are as useful as ever, but you need to be aware like Twan says, they will get expanded inline (unless they are indexed views). Theyre good for convenience and abtracting, but if you nest them too deeply, and they are complicated views, it is easy to get into a situation where an execution plan for a query is very poor, even though the query itself is very simple. In such cases it might be better to write the longwinded SQL, which then gives a chance to optimise it better

---

I agree with chappy really – nested views work and normally perform ok, as twan said, but if a developer comes along to me with performance problems and he’s got 4 layers of nested views with poorly formatted SQL in each one and expects me to pick my way through it all and find what’s wrong, then I tend to SIGH. Tom Pullen
DBA, Oxfam GB

---

Hi ya,

yes you can, but it is a lot harder to get SQL to actually use the index, since in its plans it assumes that the table has 100 rows, it will still tend to table scan… Definitely agree with the others that debugging/optimising complex nested views is way more difficult Cheers
Twan

---

That opens another question I failed to find answer so far: When to use table variable and when temporary tables? I know that table variables don’t cause sp recompilation and I know the rule that when you expect to return big number of rows you should use temporary tables instead of table variable, but I haven’t found any recommendation about how big is "big number of rows".

---

I use table variables as a first choice, although if I expect more than a couple of 1000 rows in the table then I will compare this to the same proc using a temporary table… Cheers
Twan

---

Thankyou for all the replies; very helpful. It sounds like Views are still widely used and that nested Views are ok, with the caveats that 1) some common sense analysis needs to be performed to ensure that nesting is appropriate (and efficient) for each new view.
2) probably best to keep the nesting relatively shallow in order to keep development and optimisation in check. I do have a follow-up to this though: Chappy mentioned Indexed views, which, I have to say, I have not actually used before. A quick read through BOL suggests that these would be efficient for relatively static data, but perhaps less so for frequently updated data. Is this a reasonable assumption? Has anyone had good/bad experiences with Indexed Views? Regards,
Ian

---

Hi ya,<br /><br />The best use of indexed views is for views that summarise the data in some way. e..g. a view on a 100 million row table which produces summary per customer yielding 10,000 rows would be a fantastic candidate for an indexed view.<br /><br />whereas a view which just removes some columns but still returns all of the rows out of that table would be a poor choice for an indexed view<br /><br />that’s about the two extremes… indexed views can be very good, but as always don’t over use them <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />in terms of the volatility of the data, creating an indexed views essentially stores the data in the view rather than derives it. So every insert/update/delete results in not only making that change in the source data, but then also updating the index view. if the source data is very volatile (especially updates) then the performance hit caused by the indexed view may be unacceptable.<br /><br />Cheers<br />Twan

---

I used indexed views when user wanted to have possibility for wildcard search on many columns on view that joined almost 7 or 8 tables. Base table changes were not frequent since data are refreshed a few times a day from oltp production db. I could create denormalized table, but since I anticipated occasional base table structure change I’ve gone with base table without indexes and indexes on indexed view. Indexed view must have clustered index which means that table with view rows is actually created. Rows there are updated automagically for every change in base table. This way I simplified data modification code, because base tables are normalized and I left mssql server to take care about refreshing indexed view.

---

]]>