August 1, 2001 at 2:40 pm
I got this warning message while trying to create the database, "Warning: The table 'Zeuqram_Tools' has been created but its maximum row size (9757) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes."
Is it true that it will not let me INSERT or UPDATE to this table?
Any help would be greatly appreciated.
August 1, 2001 at 2:49 pm
What it means is that the row size for the table has exceeded 8060 which is the max allowed and if u insert/update a rec which exceeds this size the insert or update WILL fail
as long as the update / insert keeps the row size within the limit u are safe ..an option would be to split the table into 2 parts
August 1, 2001 at 3:06 pm
What it means is that the row size for the table has exceeded 8060 which is the max allowed and if u insert/update a rec which exceeds this size the insert or update WILL fail
as long as the update / insert keeps the row size within the limit u are safe ..an option would be to split the table into 2 parts
Thank you very much for your help. Does SQL SERVER 2000 allow you to change the maximum number of bytes per row?
August 1, 2001 at 3:42 pm
quote:
What it means is that the row size for the table has exceeded 8060 which is the max allowed and if u insert/update a rec which exceeds this size the insert or update WILL fail
as long as the update / insert keeps the row size within the limit u are safe ..an option would be to split the table into 2 parts
Thank you very much for your help. Does SQL SERVER 2000 allow you to change the maximum number of bytes per row? Also, if the maximum number of bytes per row is only 8060 how can you store images or CLOB data?
August 1, 2001 at 6:07 pm
nope u cannot change the max size of the row...
for the blob/image/text fields the storage is different . i think in sql 7 it was implemented as a linked list and in 2000 its pointers . so the data is not actually stored as a part of the table...
August 1, 2001 at 10:05 pm
You're definitely taking a risk defining a table where there is the possibility that the data length could exceed the row capacity. Splitting the table and using triggers to maintain the 1 to 1 relationship is one way, but I'd suggest you take another look your design to see if you can't normalize this table further.
Not only do you risk your app blowing up at some point, super wide rows like this kill your io rates.
Andy
August 2, 2001 at 2:45 pm
Andy makes a good point. Pages are 8KB in SQL Server 7 to take advantage of the I/O from the OS perspective, an improvement over SQL 6.5's 2K pages. Because of this, SQL Server 2000 makes no increase in the row size in order to stay within the 8K page limit. Columns with text, ntext, and image data types are treated differently.
From Books Online:
quote:
Text, ntext, and image values are not stored as part of the data row but in a separate collection of pages of their own. For each text, ntext, or image value, all that is stored in the data row is a 16-byte pointer. For each row, this pointer points to the location of the text, ntext, or image data. A row containing multiple text, ntext, or image columns has one pointer for each text, ntext, or image column.
K. Brian Kelley
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply