13

I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table

create view dbo.MyView
as 
Select * from MyTable

Now, when I am using this view in my queries may I assume that this view will take advantage of the indexes created on the table. (i.e. work in the same way as Select * from MyTable should work)? or should I have to use Indexed views

Addition to the question

If view can take advantage of the table index, then using SELECT * FROM MyView Where SomeColumn = @someValue may impact the performance badly (if view has large amount of data , so it will behave like a heap when not indexed). How to overcome this issue ? other than using NOEXPAND (because I am using Enterprise edition)

1 Answer 1

16

A view is just a "saved query". The indexes on the base table are still used whenever you access the view.

You don't need to use an indexed view, unless the view contains an expensive logic (aggregations or joins) that you don't want to perform each time you query the view. Please note that even when the view is "materialized", the optimizer is free to access the base tables and their indexes directly and ignore the index on the view altogether.

Also note that keeping the index on the view aligned with the data in the base tables is expensive: every time you write to one of the base tables you have to update the index on the view as well.

5
  • +1 for improving my excitement ! However, I want to argue on this point. Views are like physical tables and when I reference view by it's name in other queries with where clause, then SQL might take an advantage of table index (which may be poor on views but works great on table select) but then it has to scan each row of view to get me the result (as it will become heap) Nov 12, 2014 at 14:56
  • 1
    @Zerotoinfinite "Views are like physical tables" - no, most definitely. Nov 12, 2014 at 15:03
  • @Zerotoinfinite - Non-indexed views are not scanned, since they only exist as a definition, but the referenced tables are searched and will use existing indexes if appropriate. No intermediate heap will be created, if I understand your comment correctly.
    – RLF
    Nov 12, 2014 at 15:05
  • You got me right ! So, it means that when I am querying my view on some index column (from any table used inside a view), SQL may take advantage to use clustered_index_seek Nov 12, 2014 at 15:08
  • 1
    @Zerotoinfinite - Correct. The query will choose what the optimizer determines to be "the best" choice for access. It can choose to seek/lookup/scan on any index available, whether clustered or not.
    – RLF
    Nov 12, 2014 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.