SQL Server Performance Forum – Threads Archive

Does Indexes of table gets used by views

Do the view created on tables use the indexes applied on those tables. Consider 4 tables having Primary Key with Clustered Indexes, and if a view has been created with a union all for this 4 tables, then would the indexes on those table be used when this view is used? Thanks and Regards,
Ashish Patel.

---

Yes, index will be used if query optimizer decides that execution plan with such index is the most efficient. Before execution plan for query using views is compiled, all view occurencies are replaced by query that defines the view.

---

Not sure if the UNION operator spoils the fun in this scenario.

---

So suppose, take it that i have a Table with 200 million records having a Primary Key with Clustered Index, and on weekly basis i keep adding new records to this table with few 1000k records. And then i re-index this whole table due to new addition of the records to this table. So, re-indexing the whole table would certainly take time, and even querying is going to take a bit of time. So what i was thinking of is to split this table into 4 new tables all having 50 million records with a Primary Key and Clustered Index column in each table. After this i can easily add new records and re-index the last table and untouching the other first 3 tables. Do you people think that this would be wise enough. Ashish Patel
(Team Leader)
Vadodara – India

---

Take a look at horizontaly partitioned views in BOL. Implementation of such feature is far from straight forward on 2000. On 2005 it is advertised to be much improved.

---

Thanks for this nice suggestion. Really, this would be worth thinking and applying. Thanks And Regards,
Ashish Patel

---

]]>