Row Size Limitation

Question

Post reply

Row Size Limitation

arrjay

Hall of Fame

Points: 3455
More actions
September 29, 2014 at 4:53 am

#297037

Hi there - I keep getting the following error;
Msg 511, Level 16, State 1, Line 103
Cannot create a row of size 8065 which is greater than the allowable maximum row size of 8060.
I know what is causing it but is there anyway to extending the default length as I need this field to contain over 8060 characters. Thanks.
--------------------------------------------
Laughing in the face of contention...

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Did you use variable length columns such as varchar and varbinary?

Why don't you post the table DDL so we can have a look?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Hi - Please see below my code for altering the table;

ALTER TABLE tbl_User

ADD FreeFieldValues NVARCHAR(MAX) NULL

Thanks, Russ

--------------------------------------------

Laughing in the face of contention...

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

NVARCHAR(MAX) values are not stored inside the table but in LOB pages. However, a 24-byte pointer is still stored in the regular pages.

What's the Point of Using VARCHAR(n) Anymore?[/url]

This means you can still go over the limit. So you need to take a look at the other columns as well.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arrjay Hall of Fame Points: 3455 More actions · Answer 4

Thanks for your response. I don't think the link works, can you re-port. Thanks!

--------------------------------------------

Laughing in the face of contention...

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

I fixed the link 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Koen Verbeeck (9/29/2014)
NVARCHAR(MAX) values are not stored inside the table but in LOB pages. However, a 24-byte pointer is still stored in the regular pages.
What's the Point of Using VARCHAR(n) Anymore?[/url]
This means you can still go over the limit. So you need to take a look at the other columns as well.

The link works for me. Can you post the CREATE TABLE statement for tbl_User please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

arrjay Hall of Fame Points: 3455 More actions · Answer 7

I can now access the link. Thanks for updating it. I'm having a look now.

The full DDL script is attached.

--------------------------------------------

Laughing in the face of contention...

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 8

My first thought is to get rid of the GUIDs and replace them with integer foreign keys.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arrjay Hall of Fame Points: 3455 More actions · Answer 9

Thanks but I can't change what is already there. I can update the field I have referenced but a structural change would take too long to get deployed to live.

--------------------------------------------

Laughing in the face of contention...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

arrjay (9/29/2014)
Thanks but I can't change what is already there. I can update the field I have referenced but a structural change would take too long to get deployed to live.

That's a shame, because there's scope to reduce the rowsize quite dramatically.

Use VARCHAR instead of NVARCHAR unless you need the extended character set.

Use DATE instead of DATETIME when the time component is irrelevant, such as DOB.

Use appropriate integer datatypes - Gender_i, MaritalStatus_i are probably TINYINT.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

arrjay Hall of Fame Points: 3455 More actions · Answer 11

OK, thanks for the advice. Are we saying that without making significant structural changes to the table I won't be able tot increase the number of characters in the field in question? Thanks.

--------------------------------------------

Laughing in the face of contention...

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 12

arrjay (9/29/2014)
OK, thanks for the advice. Are we saying that without making significant structural changes to the table I won't be able tot increase the number of characters in the field in question? Thanks.

No. You went over the limit of bytes allowed. The only remedy is to reduce the number of bytes used and that is done by changing data types. Or removing columns altogether 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gazareth One Orange Chip Points: 27737 More actions · Answer 13

ChrisM@Work (9/29/2014)
arrjay (9/29/2014)
Thanks but I can't change what is already there. I can update the field I have referenced but a structural change would take too long to get deployed to live.
That's a shame, because there's scope to reduce the rowsize quite dramatically.
Use VARCHAR instead of NVARCHAR unless you need the extended character set.
Use DATE instead of DATETIME when the time component is irrelevant, such as DOB.
Use appropriate integer datatypes - Gender_i, MaritalStatus_i are probably TINYINT.

Some of them are rather long too - 320 characters for an email address?

I suppose it's possible, but if someone actually had an email address that long I'd tell them to get a grip 🙂

Also, Username 320, Position 256, First/Middle/Last names 100 characters at each, all at 2 bytes per character.

Of course, as Chris says, varchar instead of nvarchar instantly halves those.

arrjay Hall of Fame Points: 3455 More actions · Answer 14

Ok, thanks.

--------------------------------------------

Laughing in the face of contention...