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

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

datamonkey
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)

datamonkey
Starting Member

29 Posts

Posted - 12/20/2006 :  19:56:48  Show Profile
Does anyone have any thoughts on this?
Thanks
DM
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.05 seconds. Powered By: Snitz Forums 2000