PRB: Column Level CHECK Constraint Required Before Implementing a Partitioned View (264160)


The information in this article applies to:

  • Microsoft SQL Server 7.0
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.
Modification Type:MajorLast Reviewed:2/4/2001
Keywords:kbprb KB264160
©2002 Microsoft Corporation. All rights reserved.