PRB: Cannot Update Distributed Partitioned Views When You Alter Base Table Constraints (270013)

SYMPTOMS

A distributed partitioned view joins horizontally partitioned data from a set of member tables across one or more SQL Servers, which makes the data appear as if it is from one table. If you run any statements that modify the data in the partitioned view after you first disable and then enable the check constraint on any of the involved base tables, you may receive the following error message:

Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'v1' is not updatable because a partitioning column was not found.

CAUSE

When you disable a check constraint, SQL Server marks the constraint as untrusted, which means that the server does not check for consistency when a user modifies rows that may violate the check constraint.

Later, when this check constraint is enabled, SQL Server maintains its status as untrusted because it cannot validate the data for that column. As a result, the Query Optimizer does not use these constraints to prune out unwanted branches in the partitioned view or treat such columns as range partitioned columns. This behavior is by design.

WORKAROUND

To work around this problem, drop and then re-create the check constraint that was altered. You can use the following query to identify the untrusted check contraints in a database:

SELECT name, status FROM sysobjects WHERE xtype = 'C' AND status &0x800 = 0x800

MORE INFORMATION

Steps to Reproduce Behavior

On Computer 1, in the Query Analyzer window, paste the following code:

USE pubs
GO
DROP TABLE t1
GO
CREATE TABLE t1 (c_int int NOT NULL)
GO
CREATE UNIQUE CLUSTERED INDEX i1 ON t1(c_int ASC)
GO
ALTER TABLE t1 ADD CONSTRAINT c1 PRIMARY KEY (c_int) 
ALTER TABLE t1 ADD CONSTRAINT c2 CHECK (c_int >= 1000)
GO

On Computer 2, in the Query Analyzer window, paste the following code:

USE pubs
GO
DROP TABLE t1
GO
CREATE TABLE t1 (c_int int NOT NULL)
GO
CREATE UNIQUE CLUSTERED INDEX i1 ON t1(c_int DESC)
go
ALTER TABLE t1 ADD CONSTRAINT c1 PRIMARY KEY (c_int) 
GO
ALTER TABLE t1 ADD CONSTRAINT c2 CHECK (c_int < 1000)
GO

On Computer 1, paste the following code:

-- Create the View Definition.
USE pubs
GO
DROP VIEW v1
GO
CREATE VIEW v1 AS
   SELECT * FROM Computer2.pubs.dbo.t1
   UNION ALL
   SELECT * FROM pubs.dbo.t1
GO

SET XACT_ABORT ON
GO

-- The following INSERTs complete successfully.
INSERT INTO v1 (c_int) SELECT 1
INSERT INTO v1 (c_int) SELECT 1100
GO

-- The following UPDATE completes successfully. 
UPDATE v1 SET c_int = c_int
GO

-- Disable the check constraint.
ALTER TABLE t1 NOCHECK CONSTRAINT c2
GO

-- Enable the check constraint.
ALTER TABLE t1 CHECK CONSTRAINT c2
GO

-- The following statements fail:
UPDATE v1 SET c_int = c_int
DELETE FROM v1
GO

-- Drop and re-create the Check Constraints.
ALTER TABLE t1 DROP CONSTRAINT c2
ALTER TABLE t1 ADD CONSTRAINT c2 CHECK (c_int >= 1000)

-- From this point forward, all of the DML statements work fine.
UPDATE v1 SET c_int = c_int