Horizontal Partitioning

We’ve got a moderately large database here – 80gb, 5 tables and 500m rows – and we had problems updating it. Following a bout of Horizontal Partitioning we’ve now got 130 tables and those rows distributed evenly across them. However when a check constraint was updated today SQL Server 2000 wouldn’t allow data to import and we got the error message:

UNION ALL view ‘Classifier_z’ is not updatable because a partitioning column was not found.

In the back of my mind i’d remembered about trusted and untrusted check constraints and after a bit of Googling (I did use Google so I can use it as a verb ;)) I found this page which displays all the currently untrusted constraints in your table, and further allows you to set them to trusted. Here’s the code too, just in case that target page disappears.

To display untrusted constraints:
[sql]
SELECT ctu.TABLE_SCHEMA, ctu.TABLE_NAME, cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS ctu
ON ctu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE objectproperty( object_id(quotename(cc.CONSTRAINT_NAME)), ‘CnstIsNotTrusted’ ) = 1
[/sql]

To make all constraints trusted:
[sql]
DECLARE @tablename nvarchar(300), @constraint nvarchar(300)
DECLARE @constraints cursor
SET @constraints = cursor fast_forward FOR
SELECT quotename(ctu.TABLE_SCHEMA) + N’.’ + quotename(ctu.TABLE_NAME), quotename(cc.CONSTRAINT_NAME)
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS ctu
ON ctu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE objectproperty( object_id(quotename(cc.CONSTRAINT_NAME)), ‘CnstIsNotTrusted’ ) = 1
OPEN @constraints
WHILE (1=1)
BEGIN
FETCH @constraints INTO @tablename, @constraint
IF @@fetch_status < 0 BREAK
EXEC('ALTER TABLE ' + @tablename + ' WITH CHECK CHECK constraint ' + @constraint)
END
[/sql]