May 3, 2013 at 3:39 pm
I have a view on one underlying table with schemabinding. I created a clustered index of five columns on it
The underlying table has a clustered index on it also, but its not on a join column, its on a date, the joining column is 3rd in the order, if that matters anymore.
I thought putting a clustered index on the view would let me preserve the underlying's schema during data loading and allow a clustered index on the view with different columns without affecting the underlying table, but when I look at the execution plan, the optimizer is using the clustered index from the underlying table!
Surprise!
What am i missing or what should i be reading?...i thought the clustered index on the view would be independent of and have precedence over the clustered index on the table, but it doesn't look that way.
thanks a lot
drew
May 3, 2013 at 4:18 pm
Look at the NOEXPAND hint. You may need that when you query the view and want to use the index on the view.
May 4, 2013 at 9:23 am
that was the missing ingredient!
thank you so much.
Why do i hear Steve Miller singing Abracadabra in the background?:hehe:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply