April 24, 2018 at 8:59 am
I am receiving the message while doing a SELECT statement.
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8274 which is greater than the allowable maximum row size of 8060.
Curious, if my SELECT is coded as below - the error occurs:
SELECT
EDGC01.*,EDGC02.*,ATT.*
But if I code (with the "top 1000") it works:
SELECT top 1000
EDGC01.*,EDGC02.*,ATT.*
Only 295 rows are returned.
April 24, 2018 at 9:13 am
john.p.lantz - Tuesday, April 24, 2018 8:59 AMI am receiving the message while doing a SELECT statement.Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8274 which is greater than the allowable maximum row size of 8060.Curious, if my SELECT is coded as below - the error occurs:
SELECT
EDGC01.*,EDGC02.*,ATT.*But if I code (with the "top 1000") it works:
SELECT top 1000
EDGC01.*,EDGC02.*,ATT.*Only 295 rows are returned.
You got lucky. You can't have a row that is more than 8,060 bytes. Check your execution plan to see if you are spilling into tempdb. That might explain it, as otherwise a SELECT of a single varchar(max) column could fail if it had more than 8,060 characters in it. I would have to wonder just how many columns you are selecting, given that you have 3 tables worth. Can any one person actually make reasonable use of that many columns? What is the objective of this SELECT? Please post the DDL for the tables involved, and the rest of that SELECT, along with the execution plan.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 24, 2018 at 9:17 am
sgmunson - Tuesday, April 24, 2018 9:13 AMjohn.p.lantz - Tuesday, April 24, 2018 8:59 AMI am receiving the message while doing a SELECT statement.Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8274 which is greater than the allowable maximum row size of 8060.Curious, if my SELECT is coded as below - the error occurs:
SELECT
EDGC01.*,EDGC02.*,ATT.*But if I code (with the "top 1000") it works:
SELECT top 1000
EDGC01.*,EDGC02.*,ATT.*Only 295 rows are returned.
You got lucky. You can't have a row that is more than 8,060 bytes. Check your execution plan to see if you are spilling into tempdb. That might explain it, as otherwise a SELECT of a single varchar(max) column could fail if it had more than 8,060 characters in it. I would have to wonder just how many columns you are selecting, given that you have 3 tables worth. Can any one person actually make reasonable use of that many columns? What is the objective of this SELECT? Please post the DDL for the tables involved, and the rest of that SELECT, along with the execution plan.
Steve is right here, I highly doubt that your query needs to return all of the columns it's potentially returning. Remove the [Table].* syntax and incldue only the columns you need in your SELECT clause; you'll probably find the problem fixes itself then.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 24, 2018 at 9:31 am
Yes, the tables are very wide. There are a total of 932 columns returned when the SQL works. There is a different execution plan when the top 1000 is used vs. when it's not included.
April 24, 2018 at 9:35 am
john.p.lantz - Tuesday, April 24, 2018 9:31 AMYes, the tables are very wide. There are a total of 932 columns returned when the SQL works. There is a different execution plan when the top 1000 is used vs. when it's not included.
Not surprised. Those 295 rows were actually retrievable, and I'm guessing there are more than 1,000 rows that should come out of that SELECT. What, pray tell, is anyone going to do with 932 columns? That's kind of ridiculous, as no one can reasonably make use of that many. Surely, they aren't ALL needed...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 24, 2018 at 9:46 am
No - only 295 rows are returned. I discovered that it does work when you put top 1,000 - even though 1,000 rows are never returned. Makes sense now why one would work and the other doesn't - because of different execution plans.
Yes I agree the SQL is kind of crazy. It's for a report - they want to display all items (aka columns) for a reporting entity. Is it very user friendly? No - but sometimes the users get what they want regardless if it makes sense.
I was always expecting the error to occur if you're creating a table. Was surprised when I saw it occur with a SELECT.
April 24, 2018 at 10:13 am
john (dba) - Tuesday, April 24, 2018 9:46 AMNo - only 295 rows are returned. I discovered that it does work when you put top 1,000 - even though 1,000 rows are never returned. Makes sense now why one would work and the other doesn't - because of different execution plans.Yes I agree the SQL is kind of crazy. It's for a report - they want to display all items (aka columns) for a reporting entity. Is it very user friendly? No - but sometimes the users get what they want regardless if it makes sense.
I was always expecting the error to occur if you're creating a table. Was surprised when I saw it occur with a SELECT.
While you are getting by for now using TOP (1000), the day may come when you can no longer rely on that working. If any of these columns are of the varchar(max) or nvarchar(max) variety, you could have a serious problem. Some rows might never be returnable because the overall width goes past 8,060, and your JOIN structure ends up having to change, and then the execution plan changes such that using TOP no longer keeps you out of tempdb... Or, they add even MORE columns...and that forces you into tempdb.. Or business volume drives that row count from 295 to over 10,000, or ... <<substitute any form of additional stuff not previously mentioned>>. You may be on borrowed time, and if you don't warn folks ahead of time that this is a possibility, then there will be shock and surprise when it arrives - none of it to your benefit...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 12:53 am
This was removed by the editor as SPAM
June 20, 2018 at 6:52 am
Lj Burrows - Wednesday, June 20, 2018 12:53 AMHere is the workaround of SQL Server Error 511. Go through it:
https://blog.sqlauthority.com/2015/05/30/sql-server-error-msg-511-using-sql-server-sparse-columns/
That does nothing to solve the problem when there's no table change involved. The problem for this thread is that merely doing a SELECT is causing the problem, as the execution plan is having to spill into tempdb, and the rows being spilled are just too large for that to succeed. Only by including the TOP operator could this user get a successful result, because it kept the execution plan from spilling data into tempdb. That's just dumb luck, and it seems unlikely that such will hold out for very long. Data growth alone will likely kill this query pretty quickly...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply