CODEWITHSUNDEEP
sql-server-2008-r2
Amy
Amy

Reputation: 31

SQL 2008 R2 Row size limit exceeded

I have sql 2008 R2 database. I created a table and when trying to execute a select statement (with order by clause) against it, I receive the error "Cannot create a row of size 8870 which is greater than the allowable maximum row size of 8060."

I am able to select the data without an order by clause, however the order by clause is important and I require it. I have tried a ROBUST PLAN option but I still received the same error.

My table has 300+ columns with data type TEXT. I have tried using varchar and nvarchar, but have had no success.

Can someone please provide some insight?

Update:

Thanks for comments. I agree. 300+ columns in one table is not very good design. What I'm trying to do is bring excel tabs into the database as data tables. Some tabs have 300+ columns.

I first use a CREATE statement to create a table based on the excel tab so the columns vary. Then I do various SELECT, UPDATE, INSERT, etc statements on the table after the table is created with data.

The structure of the table usually follow this patter: fkVersionID, RowNumber(autonumber), Field1, Field2, Field3, etc...

is there any way to get around the 8060 row size limit?

Upvotes: 3

Views: 8512

Answers (3)

Peet
Peet

Reputation: 21

You mentioned that you tried nvarchar and varchar ... remember that nvarchar doubles the bytes used, but it is the only one of the two to support foreign characters in some cases, such as accent marks.

varchar is a good choice if you can limit its maximum size appropriately. 8000 characters is still a real limit, but if on average each varchar column is no more than 26 characters, you'll be okay. You could go riskier and go with varchar and 50char length, but on average only utilize 26characters per column.. meaning one column maybe 36 character length, and the next is 16character length... then you are okay again. (As long as you never exceed the average of 26characters per column for the 300 columns.)

Obviously with dynamic number of fields, and potential to way exceed the 8000 character limit, it is doomed by SQL's specs. Your only other alternative is to create multiple tables and when you access the data, have a unique key to join appropriate records on. So in your select statement, use the join, and from multiple tables then you can handle rows with 8000 + 8000 + ...

So it is doable, but you have to work with SQL rules.

Upvotes: 2

egrunin
egrunin

Reputation: 25053

I had a legacy app like this, it was a nightmare.

First, I broke it into multiple tables, all one-to-one. This is bad, but less bad than what you've got.

Then I changed the queries to request only the columns that were actually needed. (I can't tell if you have that option.)

Upvotes: 0

Roman
Roman

Reputation: 20246

I believe you're running into this limitation:

There is no limit to the number of items in the ORDER BY clause. However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.

Upvotes: 1

Related Questions