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

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 SCHEMABINDING

Author  Topic 

datamonkey
Starting Member

29 Posts

Posted - 2006-12-18 : 16:46:33
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 - 2006-12-20 : 19:56:48
Does anyone have any thoughts on this?
Thanks
DM
Go to Top of Page
   

- Advertisement -