May 31, 2019 at 12:34 pm
Hello Team,
I am having a SQL view(having 600 columns). From last 5 years it is working fine but suddenly we are getting below error.
Msg 8618, Level 16, State 2, Line 3
The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. If the query has a GROUP BY or ORDER BY clause, consider reducing the number and/or size of the fields in the clause. Consider using prefix (LEFT()) or hash (CHECKSUM()) of fields for grouping or prefix for ordering. Note however that this will change the behavior of the query.
How can we resolve this issues without changing any code?
Thanks in Advance
May 31, 2019 at 12:48 pm
How can we resolve this issues without changing any code?
Your options are limited.
Reducing the length of one or more of the underlying table columns is the best idea that comes to mind.
Alternatively, you may be able to track down the extra-lengthy base data which has caused the error and modify that, in the knowledge that that is just a bandaid.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 31, 2019 at 12:53 pm
The answer is right there in the error message. A row size can't exceed 8k. If you want to know more about that you should read about how SQL stores data, specifically around pages. It might have worked before because you can have multiple large column data types like VARCHAR(8000) for example on one table but as long as the actual total data lenght is below 8k you'll be fine. All it took is one large string to break the camel's back. I would also seriously reconsider your design. I can't think of too many good reasons why you'd want to have 600 columns.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply