Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Easy Question - Indexes on Tables in a view Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 10:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:12 PM
Points: 567, Visits: 469
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?
Post #1367151
Posted Tuesday, October 2, 2012 10:14 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1367156
Posted Tuesday, October 2, 2012 10:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:12 PM
Points: 567, Visits: 469
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?
Post #1367157
Posted Tuesday, October 2, 2012 4:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 2,027, Visits: 3,023
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1367317
Posted Tuesday, October 2, 2012 5:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 8, 2014 10:51 AM
Points: 292, Visits: 439
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.
Post #1367344
Posted Tuesday, October 2, 2012 9:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 5,586, Visits: 24,932
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
Post #1367385
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse