I'm seeing a huge performance hit in our enterprise application when changing the XML schema collection of a given column on a large table. Simplistically put, we're doing something like this:
ALTER TABLE HugeTable ALTER COLUMN CustomFields XML
(note: CustomFields was previously bound to XML(CustomFieldsSchemaCollection, but of course we need to modify that xml schema, so we need this statement so that that schema can be modified)
And then, after modifying the CustomFieldSchemaCollection, we do this:
ALTER TABLE HugeTable ALTER COLUMN CustomFields XML(CustomFieldSchemaCollection)
The first statement takes 8 minutes and the second statement takes 10 minutes.
We found we could slightly optimize the first statement (50% performance boost) by using the following:
ALTER TABLE HugeTable ALTER COLUMN CustomFields nvarchar(max)
The effect is that the first statement takes 4 minutes and the second statement takes 10 (so, 14 min, down from 18).
Bottom line question is... Is there a way to do this "xml schema re-binding" (or whatever one calls it) in a way that avoids SQL Server's totally unnecessary and redundant checking of every value in the column? (Note: yes, we can safely assume that the existing XML data in that table will conform to the new xml schema collection.)
Thanks to anyone who can assist!