SQL 2008 R2 Row size limit exceeded
Hi,
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?
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?
ASKER
Hi Cyberkiwi,
Thanks for the feedback.
The flow of my code goes like this:
CREATE table
SELECT from table
If I use the varchar(max) datatype, I cannot create the table at all. I receive the row size error. With NTEXT or TEXT datatype, I can at least create my table.
Thanks for the feedback.
The flow of my code goes like this:
CREATE table
SELECT from table
If I use the varchar(max) datatype, I cannot create the table at all. I receive the row size error. With NTEXT or TEXT datatype, I can at least create my table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>You're right<<
That is because in order to use varchar(MAX), nvarchar(max), varbinary(max) and xml in much the same way that text and ntext is stored you need to do:
EXEC sp_tableoption 'YourTableName', 'large value types out of row', 1;
>>There really isn't much call for 300+ columns in any application, so you are better off re-thinking your application or presentation<<
I could not agree more.
That is because in order to use varchar(MAX), nvarchar(max), varbinary(max) and xml in much the same way that text and ntext is stored you need to do:
EXEC sp_tableoption 'YourTableName', 'large value types out of row', 1;
>>There really isn't much call for 300+ columns in any application, so you are better off re-thinking your application or presentation<<
I could not agree more.
ASKER
I gave this answer a B because it might have helped if he provided some tips for the front end programming.
Let us know how that goes.
You should be able to select from 300+ Varchar(max) columns, but not 300 x varchar(30) columns, if all columns contain 30 chars, because that would cause one row of result to contain 9000 bytes which is longer than that allowed per row. varchar(max) is special because it is stored off-page.