Microsoft KB Archive/264160

= PRB: Column Level CHECK Constraint Required Before Implementing a Partitioned View =

Article ID: 264160

Article Last Modified on 2/4/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q264160



SYMPTOMS
A partitioned view, run with SHOWPLAN_TEXT or STATISTICS PROFILE set ON, may not have a Filter operator upon one or more of the partioned view's subtables. A run of sp_helpconstraint against one of those subtables reports that the subtables' constraint is a table constraint (in other words, not a column constraint).



CAUSE
In SQL Server 7.0, partitioned views do not use table constraints as Filter operators; instead, partitioned views use column constraints (in other words, a check constraint specified on the partitioning column). A column constraint cannot be added to a table after the table has been created; ALTER TABLE will always add a table constraint. Column constraints can only be specified by using the CREATE TABLE statement.



WORKAROUND
To work around the problem, follow these steps:
 * 1) Copy out the data from any subtable that does not have column CHECK contraints.
 * 2) Drop the original subtable.
 * 3) Use the CREATE TABLE statement to add a column CHECK contraint to a new subtable.
 * 4) Copy the data back into the new subtable.



MORE INFORMATION
This information does not apply to Microsoft SQL Server 2000; the column constraint requirement was removed.

Keywords: kbprb KB264160

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.