Partitioned View and Pesky Error: UNION ALL view is not updatable because a partitioning column was not found

This particular situation occurred with the following architecture: we have multiple tables with check constraints on a column (a column containing the year in this case) and a partitioned view which unions all the underlying tables, and is updatable. We've been running updates and inserts on the partitioned view ever since it was implemented.

I recently ran a VS TFS Build to the production server. The change was adding a new column to the underlying tables, but not altering the primary key or constraints on those table. Suddenly we were getting the below error on any attempt to insert or update on the view:

UNION ALL view is not updatable because a partitioning column was not found

After much research, testing and banging my head on my keyboard, I eventually found the error was due to the underlying check constraints being no longer trusted. Seems like some hiccup occurred during the publish/build.

The error message was technically correct: partitioning column was not found not because it didn't exist but because the check constraints were no longer trusted.

Borrowed this bit of code I found on Brent Ozar's website to find untrusted check constraints on my database:

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname from sys.check_constraints i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;

And then issued this fix, on all untrusted check constraints, to check them.

ALTER TABLE dbo.TableName WITH CHECK CHECK CONSTRAINT CK_CheckConstraintName;

Everything is back to normal now. Phew.