INSERT or UPDATE to this table will fail if the re

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

INSERT or UPDATE to this table will fail if the re

Author

Topic

Robowski
Posting Yak Master

101 Posts

Posted - 2013-07-24 : 08:59:15

Hi,

I'm getting the error

The table [Table] has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Done some reading on google and understand the issue but a lot of posts seem to suggest this shouldn't happen in the version I'm using as it SQL should use an overflow row to store them?


SELECT @@version

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)

Can anyone advise?

Cheers

Rob

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-24 : 09:27:00

Don't know if a lower compatibility level will cause this problem or not, but that is the only thing I can think of. Can you run this query against your server and post the result?

SELECT compatibility_level
FROM sys.databases WHERE name = 'YourDatabaseNameHere'

If that does not seem to be the problem, let us wait and see what others have to say about it.

Robowski
Posting Yak Master

101 Posts

Posted - 2013-07-24 : 09:52:22

Hi James,

Thanks for coming back to me, it's 100.

Cheers

Rob

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-24 : 10:11:35

Refer to this topic:
http://msdn.microsoft.com/en-us/library/bb510680.aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-24 : 10:17:13

It would be helpful to see the create statement...

Too old to Rock'n'Roll too young to die.

Robowski
Posting Yak Master

101 Posts

Posted - 2013-07-24 : 10:19:21

Hi

I'm not seeing anything in the below that refers to the issue I have with the exceeds the allowed maximum of 8060 bytes?

Thanks

Rob

quote:
Originally posted by MuMu88

Refer to this topic:
http://msdn.microsoft.com/en-us/library/bb510680.aspx

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-24 : 11:10:38

http://msdn.microsoft.com/en-us/library/ms186981(v=sql.105).aspx

quote:
Originally posted by Robowski

Hi

I'm not seeing anything in the below that refers to the issue I have with the exceeds the allowed maximum of 8060 bytes?

Thanks

Rob

quote:
Originally posted by MuMu88

Refer to this topic:
http://msdn.microsoft.com/en-us/library/bb510680.aspx

Robowski
Posting Yak Master

101 Posts

Posted - 2013-07-24 : 11:22:11

Thanks for the response, had previously read that and was why I raised the questions. My understanding of that is that on this version I wouldn't receive the warning as it would move them into an overflow table when the row size exceeded...

quote:
Originally posted by MuMu88

http://msdn.microsoft.com/en-us/library/ms186981(v=sql.105).aspx

quote:
Originally posted by Robowski

Hi

I'm not seeing anything in the below that refers to the issue I have with the exceeds the allowed maximum of 8060 bytes?

Thanks

Rob

quote:
Originally posted by MuMu88

Refer to this topic:
http://msdn.microsoft.com/en-us/library/bb510680.aspx

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-24 : 13:38:53

quote:
Originally posted by Robowski

Hi James,

Thanks for coming back to me, it's 100.

Cheers

Rob

I don't really know what might be causing this in SQL 2008. MSDN describes one scenario where this can happen http://msdn.microsoft.com/en-us/library/ms186981(v=sql.105).aspx but I don't know how to reproduce that scenario - you have to force a varchar column (which has to be less than 900 chars long) off-row. That is the only possibility I can think of.

Does your clustered index include any varchar columns?

"The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. For more information about allocation units, see Table and Index Organization."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-24 : 13:59:40

It's only a warning. You can create hundreds of columns of VARCHAR(8000). And it will work, you only get a warning.
But you cannot physically store more than 8000 bytes on a row. It you try, you get an error.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-24 : 14:20:21

quote:
Originally posted by SwePeso

It's only a warning. You can create hundreds of columns of VARCHAR(8000). And it will work, you only get a warning.
But you cannot physically store more than 8000 bytes on a row. It you try, you get an error.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Starting with SQL 2005, this is not true. You can store more than 8000 characters. SQL Server will store the data in row overflow allocation units.

If you can create the table you can store it as well as the examples below show

-- creating the table and insertion succeeds
CREATE TABLE A1(col1 VARCHAR(8000),col2 VARCHAR(5000), col3 varchar(8000) );
INSERT INTO A1 VALUES
	(REPLICATE('1',8000), REPLICATE('2',5000), REPLICATE('3',8000));
	

-- creating the table fails
CREATE TABLE A2(col1 CHAR(8000), col2 CHAR(5000), col3 CHAR(8000));

Now if I were to create an index with varchar columns and the combined maximum length exceeded 900, I would get the warning. And the warning is real - if you try to insert something into the table that causes the index to be wider than 900 chars, the insert will fail.

TRUNCATE TABLE A1;
CREATE CLUSTERED INDEX IDX_Col1 ON A1(col1,col2,col3)

-- Warning! The maximum key length is 900 bytes. The index 'IDX_Col1' has maximum length of 8900 bytes. 
-- For some combination of large values, the insert/update operation will fail.
-- Warning: The maximum length of the row exceeds the permissible limit of 8060 bytes. 
-- For some combination of large values, the insert/update operation will fail.


INSERT INTO A1 VALUES
	(REPLICATE('1',8000), REPLICATE('2',5000), REPLICATE('3',8000));
--Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 21017 which is greater than the allowable maximum row size of 8060.
--The statement has been terminated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-24 : 14:29:34

No, you can NEVER store more than 8000 bytes on page. What happens when you use the new VARCHAR(MAX) datatype, is that a pointer of 24 bytes is stored in-row, and the actual data is stored in a number of pages off-row, just like you mentioned.
The new behaviour is how IMAGE and TEXT are stored.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-24 : 14:43:32

quote:
Originally posted by SwePeso

No, you can NEVER store more than 8000 bytes on page. What happens when you use the new VARCHAR(MAX) datatype, is that a pointer of 24 bytes is stored in-row, and the actual data is stored in a number of pages off-row, just like you mentioned.
The new behaviour is how IMAGE and TEXT are stored.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

I think you are confusing between a page and a row because originally you were saying that one cannot store more than 8000 bytes in a row. You can. A page may hold one row or many rows or part of a row (as in the case of row overflow data).

While there are performance implications to row overflow allocation units, it is logically transparent to a user in that, to them a row can store more than 8000 bytes.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-25 : 11:39:05

quote:
Originally posted by Robowski

Hi James,

Thanks for coming back to me, it's 100.

Cheers

Rob

Just to close the loop on what I was suggesting yesterday about inserts failing if the data in a column that is part of a clustered index gets pushed off-row:

In the example below, when I create the clustered index I get the warning that you saw. The second update statement fails because col2 is being pushed off-row via that update.

I don't have a good solution to your problem though (short of suggesting to use another fixed length cluster key). But at least I hope you see what the issue is

CREATE TABLE A1(col1 CHAR(7500),col2 VARCHAR(800) );
INSERT INTO A1 VALUES ('a','b');

-- the create index succeeds, but gives you the warning message you saw
CREATE CLUSTERED INDEX IDX_Col1 ON A1(col2)
-- Warning: The maximum length of the row exceeds the permissible limit of 8060 bytes. 
-- For some combination of large values, the insert/update operation will fail.


-- this succeeds
UPDATE A1 SET col2 = REPLICATE('b',547);

-- this does not succeed
UPDATE A1 SET col2 = REPLICATE('b',548);

DROP TABLE a1;

So I am guessing that your create table statement is something like this; it should give the same error message you saw:

CREATE TABLE A1(col1 CHAR(7500),col2 VARCHAR(800) NOT NULL PRIMARY KEY CLUSTERED  );

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources