Entity Framework - Row size greater than allowable maximum row size of 8060

Question

I have an entity with a binary datatype and a corresponding varbinary(max) column in SQL Server. EF creates this:

CREATE TABLE [dbo].[Attachments] 
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [FileName] NVARCHAR(255) NOT NULL,
    [Attachment] VARBINARY(MAX) NOT NULL
);

When I try to call .SaveChanges() from Entity Framework, I get an error:

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

I understand the error, there's plenty of that on Google but I don't understand why I'm getting it. Shouldn't this be managed by Entity Framework / SQL Server?

Richard

Answer 1

The only way I can see you getting this error with that table definition is if you have previously had a large fixed width column that has since been dropped.

CREATE TABLE [dbo].[Attachments] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FileName] nvarchar(255) NOT NULL,
    [Attachment] varbinary(max) NOT NULL,
    Filler char(8000),
    Filler2 char(49)
);

ALTER TABLE  [dbo].[Attachments] DROP COLUMN Filler,Filler2

INSERT INTO [dbo].[Attachments]
([FileName],[Attachment])
VALUES
('Foo',0x010203)

Which Gives

Msg 511, Level 16, State 1, Line 12 Cannot create a row of size 8075 which is greater than the allowable maximum row size of 8060.

If this is the case then try rebuilding the table

ALTER TABLE [dbo].[Attachments] REBUILD