February 24, 2014 at 3:19 am
Been looking for more in-depth information on clustered-index views and how they work exactly.
I know, unlike a non-index view, one of the main differences is that they physically store the data.
What I am trying to find out is how exactly the view is changed when changes are made to the underlying table's/columns used in the view? Is there a sort of CDC style reference on the columns used in the view and when a change is made, it changes only the relevant rows in the view or does it re-compile the view every time any of the underlying tables are changed?
We have a scenario were every night where a table is truncated, then a huge fact table is rolled up through a proc, aggregated, put into a #HashTable and the previously truncated table re-built by selecting the contents of the #HashTable into it. Trying to understand in-depth how an clustered index view works to see if it is a better solution.
Cheers.
February 24, 2014 at 3:22 am
It works much like nonclustered index on a table do. When a row in the table is changed, the indexes are updated appropriately. Indexed view gets much the same treatment.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2014 at 3:56 am
Great thanks.
What would the best way be to test the performance/improvement if any of using a clustered index view?
With he proc I can run the code, use the execution plan, judge but over all time. But the view would be affected during any changes to the source tables? Would that be noticeable when changing the source table?
I.E would the execution only finish if say updating half a million rows in the source table once they were updated and the view's indexes updated? or would it happen when you query the clustered index view view?
February 24, 2014 at 4:15 am
roblew 15918 (2/24/2014)
What would the best way be to test the performance/improvement if any of using a clustered index view?
Same way you test the performance of anything, benchmarks without, benchmarks with, compare. (Execution plans aren't benchmarks)
I.E would the execution only finish if say updating half a million rows in the source table once they were updated and the view's indexes updated? or would it happen when you query the clustered index view view?
Already answered that. Same as nonclustered indexes, when the source table gets updated, the nonclustered indexes get updated. Indexed view behaves the same way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2014 at 8:17 am
What would the best way be to test the performance/improvement if any of using a clustered index view?
<Ans> You can drop or disable the index before load and then recreate it or rebuild it after load. This will speed up your load. Transactional environments are less suitable for indexed views. Environments that are best suited for indexedviews are data warehouses, data marts, OLAP databases, and the like
With the proc I can run the code, use the execution plan, judge but over all time. But the view would be affected during any changes to the source tables? Would that be noticeable when changing the source table?
<Ans> Create the index view with schemabinding option, which guarantees that the underlying table structure cannot be altered without dropping the view.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply