Monday, March 8, 2010

ALTER COLUMN on XML column results in Error 511

We frequently change the schema that constrains an XML column in one of the tables. When it’s time to update the schema collection and re-type the column, we use the following steps:

  1. Remove the type with the ALTER TABLE statement:
    ALTER TABLE <table> ALTER COLUMN <xml-column> XML;
  2. Change the schema collection by recreating it.
  3. Reset permissions:
    GRANT EXECUTE ON XML SCHEMA COLLECTION…
  4. Reapply the schema constraint to the column:
    ALTER TABLE <table> ALTER COLUMN <xml-column> XML(<schema collection>)

Twice I’ve started getting error 511 on step one:

Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8073 which is greater than the allowable maximum of 8060.
The statement has been terminated.

This thread from the SQL Server XML forum indicates that the behavior is “by design.” There seems to be a limit to the number of times that you can alter a column. The good news is that there is a work-around. Rebuilding the indexes on the table appears to reset the alter-count.