18

I am trying to alter a column in a table. The existing table is like this:

CREATE TABLE [dbo].[table](
   [id1] [int] NOT NULL,
   [id2] [int] NOT NULL,
   [id3] [int] NOT NULL,
   [name] [nvarchar](255) NOT NULL,
   [id4] [int] NOT NULL,
   [xmlData] [xml](CONTENT [dbo].[xml_schema]) NULL,
   [booleanData1] [bit] NOT NULL,
   [notes] [varchar](4096) NULL,
   [id5] [int] NULL,
   [booleanData2] [bit] NULL,
   [id6] [int] NULL,

   CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED 
   ([id1] ASC, [id2] ASC, [id3] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now I am trying to execute this sql on this table:

ALTER TABLE [table] ALTER COLUMN [xmlData] XML

so that I can drop the xml schema and replace it with new one.

But I am getting this error:

Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060.

Can anyone tell me what is the problem here?

1 Answer 1

26

If you have previously removed or modified columns on this table, you may need to reclaim space before this operation will succeed. SQL Server does not always/usually immediately reclaim space for dropped or altered columns.

If the previous operations were all removals (or alterations) of variable-length columns, issuing DBCC CLEANTABLE ought to be sufficient. Otherwise, you will need to rebuild the table. You can do this by rebuilding the clustered index:

ALTER INDEX PK_table
ON dbo.table
REBUILD 
WITH (ONLINE = ON); -- Enterprise only, optional

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy