Alter column for an xml data type may cause row size exceeds limit

When changing a large data type column (e.g. xml data type) in a table several times, you may get the an error saying that row size exceeds the 8060 limit.
CREATE TABELE dbo.XmlData ( id int identity not null, xmlData xml null PRIMARY KEY CLUSTERED PK_XmlData (id))
CREATE XML SCHEMA COLLECTION dbo.xmlSchema AS ‘
<xs:schema id="Test" targetNamespace="http://www.my.com/MySchema" xmlns:xs="http://www.w3.org/2001/XMLSchema"
 attributeFormDefault="unqualified" elementFormDefault="qualified">
  <xs:element name="Objects">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Version" type="xs:string"/>
        <xs:element name="ObjectOption" type="xs:int" />
        <xs:element name="Object" minOccurs="0" maxOccurs="unbounded">
          <xs:complexType>
            <xs:attribute name="ObjectID" type="xs:string" />
            <xs:attribute name="ObjectName" type="xs:string" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>’
GO
— Populate some data to the table. Run it multiple times
INSERT dbo.XmlData (xmlData) VALUES ‘
<Objects xmlns="http://www.my.com/MySchema">
 <Version>1.0.0</Version>
 <ObjectOption>3</ObjectOption>
 <Object ObjectID="10" ObjectName="MyObj1"/>
 <Object ObjectID="11" ObjectName="MyObj2"/>
 <Object ObjectID="12" ObjectName="MyObj3"/>
 <Object ObjectID="13" ObjectName="MyObj4"/>
 <Object ObjectID="14" ObjectName="MyObj5"/>
 <Object ObjectID="15" ObjectName="MyObj6"/>
</Objects>’
ALTER TABLE dbo.XmlData ALTER COLUMN xmlData xml(dbo.xmlSchema) NULL
GO
ALTER TABLE dbo.XmlData ALTER COLUMN xmlData xml NULL
GO
Run the two alter table commands several times, you will get the error:
Msg 511, Level 16, State 1, Line 1
    Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
    The statement has been terminated.
This is because "Alter column" adds a column and removes a column. Once you’ve done this the space for the old column on the pages is not reclaimed.
Microsoft claimed this is by design.
The workaround is to rebuild the clustered index for the table. If the tables does not have a clusered index, we need to create one and drop it later. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.
 
ALTER INDEX PK_XmlData ON dbo.XmlData REBUILD
 
Reorganize the index does not work. So the following command can’t solve the issue:
ALTER INDEX PK_XmlData ON dbo.XmlData REORGANIZE
I encountered the issue when I was upgrading/debuging a nvarchar(max) columns to a typed xml. By searching the internet, I
found the following thread talking about exact same problem and found the nice workaround:
"http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=745248&SiteID=1"
 
 
Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s