CODEWITHSUNDEEP
sql-server-2008
David P
David P

Reputation: 411

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

I have seen this question asked quite a few times and most of them end with a logical explanation. My table doesn't seem to be anywhere near the maximum row size.

My Dev Server is SQL 2008 Express Edition

This is my table definition. I have one varchar(max) column and the rest of my columns should be tiny. The "Notes" filed didn't contain much text, only a few characters.

CREATE TABLE [dbo].[PegBoard](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [OwnersCorporationID] [int] NOT NULL,
    [PegNumber] [int] NOT NULL,
    [DepositRequired] [bit] NOT NULL,
    [KeyRegister] [bit] NOT NULL,
    [Notes] [varchar](max) NULL,
    [Locked] [bit] NOT NULL,
    [NonLoanable] [bit] NULL,
    [DepositAmount] [decimal](10, 2) NULL,
    [LastReviewedDate] [datetime] NULL,
 CONSTRAINT [PK_PegBoard] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [PegBoard_PN_Cnst] UNIQUE NONCLUSTERED 
(
    [PegNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

When I insert a row I receive the following message.

Error:System.Data.SqlClient.SqlException: Cannot create a row of size 8066 which is greater than the allowable maximum row size of 8060.

The statement has been terminated. I have seen a similar warning when I added new columns to the table but it didn't seem to cause a failure until now.

Any ideas what could cause this problem and what I might try to fix it.

Thanks in Advance David

Update

Here is the structure of my Audit Table if that helps. The trigger itself is quite complicated as it was generated from some code I found that does all tables automatically. 

CREATE TABLE [dbo].[Audit](
[AuditID] [int] IDENTITY(1,1) NOT NULL,
[Type] [char](1) NULL,
[TableName] [varchar](128) NULL,
[PrimaryKeyField] [varchar](1000) NULL,
[PrimaryKeyValue] [varchar](1000) NULL,
[FieldName] [varchar](128) NULL,
[OldValue] [varchar](1000) NULL,
[NewValue] [varchar](1000) NULL,
[UpdateDate] [datetime] NULL,
[UserName] [varchar](128) NULL

) ON [PRIMARY]

Upvotes: 2

Views: 5161

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

It is a common misunderstanding, that using VARCHAR(MAX) is a good idea in any cases... If you really have to deal with strings larger than 8000 bytes you could think about VARBINARY(MAX) or XML.

Read this: https://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar(n)-anymore/

But it may work: Read this: why row insert above 8053 bytes not giving error when it should because max allowed row limit is 8060

Another problem with VARCHAR(MAX) is, that in some statements the implicitly used data type is the "normal" varchar and you need extra casts: Read this: https://stackoverflow.com/a/33031838/5089204

Conclusio: If you do not expect really large text it's better to use a VARCHAR(XX)definition.

Upvotes: 1

Related Questions