October 2, 2012 at 10:07 am
If I have the following query as a view:
select
b.col1,
b.col2,
b.col3,
b.col4,
b.col5
from
a
inner join b
on a.id = b.fk_id
Does it make sense to put an index on table B like this -
create nonclustered index [idx_myview_covering] on b
(
fk_id ASC
)
include ( col1, col2, col3, col4, col5 )
If not, what index should be used?
October 2, 2012 at 10:14 am
Sure, you can add that index. I'm not sure why that view would exist, since simply selecting from b would be easier and would do the same thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 2, 2012 at 10:18 am
The actual view is more complicated and makes selections from table A, as well as a few other tables.
I guess my main curiousity is in regards to the index scans that I'm getting on B. I'm thinking that, given that I'm not being very selective at all on table B, the index scan is probably the best case. That said, is there any benefit to having this index then?
October 2, 2012 at 4:00 pm
I wouldn't duplicate l lot of columns for a covering index w/o real research showing it was 100% necessary.
You should consider the possibility of clusterting tableB on fk_id, but that too will take some research to properly determine.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 2, 2012 at 5:31 pm
The index is scanned because it is retrieving all the rows in table B.
Regarding your question about if the index is useful or not, the proper response would be, "It depends", becuase one of the main advantages of a covering index is that the query do not reach the actual table, for example you have a table with a high row size, definitely the covering index you created would be very useful, however if your table has 6 columns and your covering index has 5 then I don't think it would be useful.
October 2, 2012 at 9:13 pm
Like many things in SQL "It depends" is most likely the most consistent.
Read this and with the knowledge gained test the use of the indexed view, and determine if the T-SQL statements run faster or not.
http://msdn.microsoft.com/en-us/library/dd171921(SQL.100).aspx
The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications
So try it in a non-production DB and check if it performs to your needs.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply