Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

29 Posts

Posted - 12/18/2006 :  16:46:33  Show Profile
Hi all,
I was wondering if someone could tell me why I am unable to switch data out of a partitioned base table when an indexed view is created on it... obviously SQL 2005 rules state the view schema bound to the base table so the base table cannot have any schema changes applied to it ie column name data types ect. But the odd thing here is that as far as I know a scheme boundary really isn’t a schema value depended option. In other words it doesn’t affect the schema result of the base table at all. It only affects the data segregations and potential location right?

so with that said. if you had created a base table and applied a partition scheme to it then later created an indexed view on that base(now partitioned) table (additionally the clustered index that was built on the indexed view was also partitioned using the same partition scheme that was used for the base table partitioning.)
Why is it that you cannot perform any partition maintenance to the table in question? Like switching data out or in.

in a nut shell I have successfully built a rolling sliding window partition maintenance routine that build partitions and removes old one base on a retention period variable. (Which work fine, just as long there isn’t a indexed view bound to the base table.)

SQL forces me to unbinding the view (which blows away the clustered index.) then apply any partition maintenance.
Problem with this is tow things, one the index view is a production view that is in use two the clustered index on this view takes 10 plus hours to build (millions, millions of aggregation record) times eight other index views.

I have read the BOL numerous times concerning SCHEMABINDING and have failed to see why you can't perform metadata switching at the data level (partitioning)

Starting Member

29 Posts

Posted - 12/20/2006 :  19:56:48  Show Profile
Does anyone have any thoughts on this?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000