SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Easy Question - Indexes on Tables in a view


Easy Question - Indexes on Tables in a view

Author
Message
TheGreenShepherd
TheGreenShepherd
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 603
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?
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23351 Visits: 9730
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
TheGreenShepherd
TheGreenShepherd
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 603
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?
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7831 Visits: 7145
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
mvillegascuellar
mvillegascuellar
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 483
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.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7787 Visits: 25280
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.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search