maximum row size exceeds the allowed maximum of 8060 bytes

Question

Post reply

maximum row size exceeds the allowed maximum of 8060 bytes

o1000

SSC Veteran

Points: 284
More actions
November 5, 2007 at 3:53 pm

#179036

/* Why when I create 307 columns using the SQL bellow it runs just file,
but when I create 308 (or more) columns it gives a warning?
MSSQL 2005 */
DECLARE @sql NVARCHAR(MAX)
DECLARE @col_number INT
DECLARE @total_cols INT
SET @total_cols = 307 --or 308
/*
307 runs just fine
??? WHY ??? the 308 will give a warning:
Warning: The table "#temp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
*/
SET @col_number = 0
SET @sql = 'CREATE TABLE #temp ( '
WHILE @col_number < @total_cols
BEGIN
SET @col_number = @col_number + 1
SET @sql = @sql + '[column ' + LTRIM(STR(@col_number)) + '] VARCHAR(75), '
END
SET @sql = @sql + ') SELECT * FROM #temp'
EXEC sp_executesql @sql
Thanks in advance

Viewing 9 posts - 1 through 9 (of 9 total)

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

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

Take a look here:

http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/16/geek-city-maximum-number-of-row-overflow-columns.aspx

and its follow-up here:

http://sqlblog.com/blogs/kalen_delaney/archive/2007/10/09/geek-city-detecting-overflowing-columns.aspx

Kalen explains it much better than I could.

o1000 SSC Veteran Points: 284 More actions · Answer 2

Thank you Pam, much appreciated for the links.

Now it makes perfect sense...

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Ummm... no... what doesn't make sense is why anyone would need to have more than 300 NVARCHAR(MAX) columns in a table...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pam Brisjar SSChampion Points: 12094 More actions · Answer 4

Yeah well, I wasn't going to go there, Jeff. My appetite for worms ain't that big.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

Heh... I know what you mean, Pam... I've just got this insatiable curiosity... 😀

So, what say thee o1000? Why do you need to make over 300 NVARCHAR(MAX) columns?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

This must be one of those new sideways databases in which there is a single record with each piece of data in it's own column.

Sorry Pam

Jeff has a really good head for this - he wants to know what you are actually trying to do because he is getting ready to suggest an alternative solution. I think I can hear him getting ready to type...

From the issue you have run into, it is worth trying to explain what you are doing.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

Heh... Michael, thank you for the high compliment! Made my day... 🙂

o1000, Michael is correct... what you want to do just seems tremedously odd and I want to see if I can fix the problem of having so many incredibly wide columns... what is it that you're trying to do?

And, looking at your code a bit closer this time, I see that you're really trying to create 307 VARCHAR(75) columns. Not including the "hidden" bytes required to actually define a varchar column, that's 23025 bytes which greatly exceeds the 8060 bytes allowed for row sizes (not including VARCHAR(MAX), of course).

So, the question again is why? Why do you need more than 300 VARCHAR(75) columns? What is it that you're trying to do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 8

Always consider the source.

I readily admit that I am an idiot and a poor judge of character.