November 17, 2003 at 9:27 am
Is there any rows amount limitation for Updatable Partitioned Views?
I have a big table what I split every week(weekly amount of data ~ 5-7 million records).
I'm testing updatable partitioned view for these tables. There is a problem - when I ALTER view with 6th or 7th table the view becomes unupdatable, I receive next error :
"UNION ALL view 'vw_archive_test1' is not updatable because the definition contains a disallowed construct". I created different views with table overlap, but it didn't help. After I counted records before and after error I noticed that view becomes unupdatable if count(*) more 40 million. Is anybody has the same problem?
November 17, 2003 at 12:02 pm
Not had the exact same problem but happened to have come across similar situations and stayed with what worked while following up
with the vendor and they came out with nothing different
Mike
November 18, 2003 at 6:29 am
The problem is not the number of rows, but that you need to check existing data on your check constraints for those tables. I have a 15 table partitioned views with an average row count of 30 million. Two of those tables have 90 million rows in them.
The problem is with your check constraint.
November 18, 2003 at 7:20 am
I checked all tables they have all requirements for updateble partitioned view. Also I created different views with table overlap. For example 'vw_archive_test1' holds table1-table6 and updatable. After I add table7 it's not updattable. Then I created 'vw_archive_test2' that holds table4-table10 and it's still updatable, it becomes unupdatable when I add additional table. May be this problem because I'm doing this test on developer version sql server 2000.
November 18, 2003 at 8:50 am
There must be a conflict between the tables constraints. A check constraint is overlapping between base tables. That is the only thing that could cause this type of action. If you could send me a script of your base tables with views definition, I will be glad to find the problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply