3

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?

Amy
  • 31
  • 1
  • 3
  • 3
    300+ columns with the type TEXT? Really? Sounds like that database could use some normalization. – Justin Niessner Apr 11 '11 at 22:58
  • Could you show your table layout? – chris polzer Apr 11 '11 at 23:12
  • "300+ columns with data type TEXT." is simply a very poor design. TEXT does not mean text. Any table with more than approx. 100+ columns is a possible candidate for splitting. – Mitch Wheat Apr 12 '11 at 00:29
  • There probably isn't a way to get around the 8060 table limit, but there might be another way to accomplish what you're trying to do with the data. So, what're you trying to do with the data =)? – Roman Apr 13 '11 at 15:02
  • Come to think of it, if you don't need to order on all 300 columns you could do a first `select` on just the columns you plan to filter/sort on, then join the result back to the original table and actually select all the columns you need. Might not perform well, but could work. – Roman Apr 13 '11 at 15:16

3 Answers3

2

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.

Peet
  • 21
  • 2
1

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.

Roman
  • 19,581
  • 6
  • 68
  • 84
  • @Mitch Wheat: That statement isn't entirely true. At least the documentation for [SQL Server 2008](http://msdn.microsoft.com/en-us/library/ms143432.aspx) says: "SQL Server supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online." – Roman Apr 12 '11 at 19:59
  • What we're trying to do is bring tab from excel into a database as tables. There are some tabs with 300+ columns. I agree with R0MANARMY that the intermediate tables' row size is causing my issue, but is there anyway to get around it? – Amy Apr 13 '11 at 13:19
0

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.)

egrunin
  • 24,650
  • 8
  • 50
  • 93