SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INSERT or UPDATE to this table will fail if the re
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Robowski
Posting Yak Master

101 Posts

Posted - 07/24/2013 :  08:59:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/24/2013 :  09:27:00  Show Profile  Reply with Quote
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.
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 07/24/2013 :  09:52:22  Show Profile  Reply with Quote
Hi James,

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

Cheers

Rob
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/24/2013 :  10:11:35  Show Profile  Reply with Quote
Refer to this topic:
http://msdn.microsoft.com/en-us/library/bb510680.aspx
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 07/24/2013 :  10:17:13  Show Profile  Visit webfred's Homepage  Reply with Quote
It would be helpful to see the create statement...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 07/24/2013 :  10:19:21  Show Profile  Reply with Quote
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



Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/24/2013 :  11:10:38  Show Profile  Reply with Quote
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





Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 07/24/2013 :  11:22:11  Show Profile  Reply with Quote
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







Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/24/2013 :  13:38:53  Show Profile  Reply with Quote
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."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/24/2013 :  13:59:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/24/2013 :  14:20:21  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/24/2013 :  14:29:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/24/2013 :  14:43:32  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/25/2013 :  11:39:05  Show Profile  Reply with Quote
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  );

Edited by - James K on 07/25/2013 11:42:52
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000