SQL Server Performance

Does Indexes of table gets used by views

Discussion in 'SQL Server 2005 General Developer Questions' started by asvforce, Feb 28, 2006.

  1. asvforce New Member

    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.
  2. mmarovic Active Member

    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.
  3. Adriaan New Member

    Not sure if the UNION operator spoils the fun in this scenario.
  4. asvforce New Member

    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
  5. mmarovic Active Member

    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.
  6. asvforce New Member

    Thanks for this nice suggestion. Really, this would be worth thinking and applying.

    Thanks And Regards,
    Ashish Patel

Share This Page