If you’re a SQL Server 2005 user, you’re probably thinking, “I can just use table partitioning instead of indexed views.” When you were trained on SQL Server 2005, the instructor probably told you to replace SQL Server 2000 partitioned views with SQL Server 2005 partitioned tables. Partitioned tables physically separate rows; partitioned views logically separate rows. Just as in “Supertypes and Subtypes” where we had disjointed and overlapping conditions, you might have rows in the dimension that participate in more than one role (i.e., an overlapping condition). If you ever have an overlapping condition, your best approach is to use indexed views, even in SQL Server 2005.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Hi Michelle,

Could you give me illustration how overlapping condition looks like. Because in my mind, there are customer indexed view (where PersonType is customer) , Employee indexed view (where PersonType is employee) and Trainer index view (where PersonType is trainer), they refer to Person table that has column PersonType is customer or employee or trainer-type. Let say, there is overlapping condition to display either employee or customer that has age below than 30, so I still have to query to both views or directly query to base table. In partitioned table/partitioned view, still I have to query to those objects(table/view). So could you show me the advantage gained from using indexed view instead of partitioned table/view?

Thank you.

Kasim Wirama

wirama@cbn.net.id- December 02, 2007

Article Rating 4 out of 5