July 21, 2008 at 6:14 am
Hello Guys!!
I have a question.
if there are three tables, all indexed on a couple of columns.
and a view that is using all three tables joined on those indexed columns
then, should i make indexed on vies as well?
whats the point of indexing views when all the source tables are indexed???
July 21, 2008 at 9:48 am
It is impossible to really provide a good answer to your question about whether to index the view or not without seeing execution plans and other information. I would say just off you wrote that no, you probably shouldn't index the view. I only index views where there is a computed column that will be utilized as a filter point on the view. Otherwise, in general, my indexes on my tables cover the view for the most part correctly.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
July 21, 2008 at 10:11 am
As Jonathan points out, this needs a lot more detail for a full evaluation and having some execution plans to look at helps substantially.
However, there are many cases I have dealt with where the performance improvement of doing the join in the view and indexing the view can bring large improvements over simply indexing the table. This is especially true when the tables involved are large and you are forced to join on a large datatype like a long varchar. Remember that indexes on the table will make the comparisons and searches during the join easier on the query analyzer. Indexing the view means that it does not have to do those comparisons at all.
There are a lot of factors that go into if and how much of a difference that makes, but in some cases it can make a big difference.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply