Changing the xml schema collection is a huge success
I see tremendous success in our enterprise application when modifying the XML schema collection of a given column on a large table. Nicely, we do something like this:
ALTER TABLE HugeTable ALTER COLUMN CustomFields XML
(note: CustomFields was previously bound to XML (CustomFieldsSchemaCollection, but of course we need to change this xml schema, so we need this statement so that this schema can be changed)
Then, after modifying CustomFieldSchemaCollection, we do the following:
ALTER TABLE HugeTable ALTER COLUMN CustomFields XML(CustomFieldSchemaCollection)
The first operator takes 8 minutes and the second operator takes 10 minutes.
We found that we can optimize the first statement a bit (50% performance improvement) using the following:
ALTER TABLE HugeTable ALTER COLUMN CustomFields nvarchar(max)
The effect is that the first operator takes 4 minutes, and the second operator takes 10 (so, 14 minutes, less than 18).
Bottom line ... Is there a way to do this "xml schema cross-reference" (or something similar) in a way that avoids SQL Server completely unnecessary and redundant validation of every value in the column? (Note: yes, we can safely assume that the existing XML data in this table will match the new xml schema collection.)
Thanks to everyone who can help!
source to share
If time is really a big issue (which doesn't really matter in a one-time update), you could just delete the underlying data, re-bind to the new schema, then do a bulk insert, turning all identity inserted, etc. ??
Or for a super step by step, write a script that does the following in batches:
- Modify the table and add a new XML column with a new schema
binding - Set new column data = new old column data
- Drop the old column.
- Rename the new column to the name of the old column.
- Change ordinality if necessary (another topic ... And if all your consumer queries are not written securely by specifying column names rather than relying on the underlying ordinariness)
source to share