Design Question
-
Every now and then a developer will give me a script to create a table that produces the following error:
Warning: The table 'WA_6_RecordsforReview' has been created but its maximum row size (14634) 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.
Now I'm thinking of no longer accepting CREATE TABLE statements that produce this error, but have mixed emotions. So I'm asking the forum for there opinion.
Should I send the developer back to the design table and make them get the max record length down to 8060? Or should I let is slide? Please provide your opinion.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
My question is are you in charge of maintenance of this beast?
When someone else decides to use the table and basic commands fail who will they call? The developer? You?
I ask, because where I work there is turnover, and it's not treat to come along and find someone else's database doesn't quite work the way you assume it does.
If that is not the issue, and they - the developers - will be the only one who works with the table (ever), I would still send it back and tell them why.
But that's just me.
Patrick
Quand on parle du loup, on en voit la queue
-
I would send it back as it is bad practice and causes rows to be on more than one page (excluding text fields). We have a couple tables that slipped by and now we see that error too. I recommended they be changed. They were using huge varchar fields when it wasn't necessary to have them be that big. Usually, if you force them to look at it they will find a way to shrink it.
Darren
Darren
Darren -
Hi Greg,
quote:
Every now and then a developer will give me a script to create a table that produces the following error:Warning: The table 'WA_6_RecordsforReview' has been created but its maximum row size (14634) 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.
I haven't come across this issue. Would you mind posting the CREATE TABLE statement, so I can get an impression?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
There are occasions when your tables will have this warning and still be acceptable. I typically get this error when I work with the sql_variant data type, which has a maximum length of 8016 bytes. You only have to include a few additional fields before the maximum possible data size is larger than 8060 bytes.
-
No real difference of opinion from me. I would send it back too primarily because it is sloppy irrespective of whether it is ever going to cause a problem.
-
We faced a similar issue where we designed a table that exceeded 8060 bytes...
our design was something like this :
CREATE TABLE [KeyResultAreas] (
[UserID] [int] NOT NULL ,
[AppraisalID] [varchar] (15) NOT NULL ,
[KRAType] [int] NOT NULL ,
[Weightage] [smallint] NULL ,
[KRADesc] [varchar] (3000) NULL ,
[Goal] [varchar] (3000) NULL ,
[MeasurementCriteria] [varchar] (3000) NULL ,
[SupportRequired] [varchar] (3000) NULL ,
[StatusOfKRA] [varchar] (1000) NULL ,
[Comments] [varchar] (1000) NULL
the problem was that the user could fill in information of varying lengths in the columns...i.e some users would go to the limit and fill in upto 3000 characters in KRADesc and Goal columns and fill the other columns with very little data whereas other would fill other columns to upto 3000 characters etc etc....
and to ensure that the users wouldn't get a "String or binary data will be truncated" error we used a front end validation to ensure that a row would not exceed 8060 bytes....
now this might lead to page splits and a subsequent degradation in performance but we ( not very scientifically - we're just developers playing the role of DBA due to lack of resources
)decided that this was a better option to denormalization or splitting the table and having extra joins all over the place...and so far (touch wood??) we haven't had any issues with page splits.... -
Hi winash,
quote:
CREATE TABLE [KeyResultAreas] ([UserID] [int] NOT NULL ,
[AppraisalID] [varchar] (15) NOT NULL ,
[KRAType] [int] NOT NULL ,
[Weightage] [smallint] NULL ,
[KRADesc] [varchar] (3000) NULL ,
[Goal] [varchar] (3000) NULL ,
[MeasurementCriteria] [varchar] (3000) NULL ,
[SupportRequired] [varchar] (3000) NULL ,
[StatusOfKRA] [varchar] (1000) NULL ,
[Comments] [varchar] (1000) NULL
though I prefer the use of text fields, your statement works fine on SQL7 for me???
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
The table will be created without any problem...
if created using QA then a message saying "The total row size (14061) for table 'KeyResultAreas' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added." is displayed...
(If created using EM then no message is displayed...)
The problem comes in (I think) with the 8K page size and having such a large table will result in page splits (and sparsely populated pages due to the row size) and a not very optimal space utilization....
regarding using text fields - since text fields can store upto 2GB of data (and since text fields come with the additional overhead(??) of data being stored in a different page) we tend to use text fields only in cases of very large data...
-
Hi winash,
quote:
Hi Frank,The table will be created without any problem...
if created using QA then a message saying "The total row size (14061) for table 'KeyResultAreas' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added." is displayed...
I've done it via QA without problems!
The use of text vs. varchar(x000) seems to be like everything a case of 'it depends'. Each has it own pros and cons. If your solution works, don't change it
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
>>I've done it via QA without problems!
??????....is there any option in QA to turn off warning messages...I always get that warning whenever any table created goes above 8060 bytes...
and regarding the text v/s varchar(and other similar 'it depends' scenarios) -- you are ferpectly right...
-
Hi winash,
only thing I can think of is SET ANSI_WARNINGS ON/OFF, but that's something different.
I'm pretty sure, I haven't changed settings since I have installed QA.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The table will be created, but if an attempt to insert or update a row resulting in a row that is greater in size than can fit on a page (generally 8060 bytes) then the insert/update will fail. This generally happens when you have multiple varying length fields. There are legitimate situations where you may have multiple varying length fields, but they will not all be maxed out at the same time.
This is not an error message so much as a warning.
If you feel that your developers are sharp enough that they will either code the logic into their application to check for a row being too big before attempting an insert/update, then this doesn't present a problem. You sould let them know that this is the case and they should ensure that the application doesn't allow any too big rows.
If you cannot trust your developers to safeguard against this, or if free data entry (for instance via Query Analyzer) is allowed where the developers cannot protect the table against insering/updating to too large a size, then don't create the table, or change its design.
Just my opinion, but it's worked for me for several years.
Matthew Bando
-
As a developer, I was pretty exicted with the version of SQL Server that allowed me to create an 8000 byte field instead of using a text field. Little did I know that I could only have one 8000 byte field per table. Maybe your developers aren't aware of the 8060 record limit. Educate them! Most developers like to learn new things...
-
This practice seems sloppy, and is probably due to insufficient knowledge on the part of the developer. Some friendly education may be appropriate.
On the other hand, there may be a compelling reason for creating the table with the potential for excessive row size. I feel a review of the business need is in order. If it's legitimate, then there are two choices. Validate the row size at the application, or with an Instead Of Trigger. One or the other has to be done, if you want to ensure error messages don't occur. Because one thing is certain, SQL Server will not accept an oversized row.
Larry Ansley
Larry Ansley
Atlanta, GA
)decided that this was a better option to denormalization or splitting the table and having extra joins all over the place...and so far (touch wood??) we haven't had any issues with page splits....Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply