Maximum Row Size

[ddeegan]

Hello

I am with a company that is using a 3rd party software and adding many fields to the primary table.

When adding more fields - message appears - Maximum row size (9457) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the row length exceeds 8060 bytes.

This means it would only be true if the maximum amount of characters were stored in a varchar field. So, if a record was INSERTED or UPDATED - it would ONLY encounter a problem if the record exceeded 8060 bytes - correct? (This would seem to be rare)

Are there any other concerns with having a table designed this way?

I would like to see this data more normalized but I am not a DBA. I want to have the database design changed - but looking for good reasons to justify redesigning some of the interface - any suggestions or helpful documentations?

Thanks
Dave

 

[gmmastros]

>> but looking for good reasons to justify redesigning some of the interface

How about.... hard to find bugs.

Here's what will eventually happen. 3 years from now, your customers will say... Your application isn't saving all of my data. I only typed 20 characters in to this field, but it's only saving the first 3 characters.

You will, of course, have fogotten all about this issue and will begin pulling out your hair until you either 1) fix the problem or 2) run out of hair.

I recommend you fix the problem now. In fact, the problem will only get worse because 6 months from now, someone will want you to add more fields to to this table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ddeegan]

Thanks George - I see the logic in what you are saying.

Our customers are internal users who are pretty adamant about how and where they want the data displayed. In order to have it displayed on the screen the way they want - the field needs to be on the primary table.

If I can come up with enough reasons why it is a bad idea, I may be able to persuade my supervisor to allow the interface to be changed (having the user do an extra click)
in order to have a better database design.

But right now, wouldn't the "not saving data" issue only come up if all of the fields were populated?

Thanks
Dave

 

[gmmastros]

Yes. The issue will only occur if all the fields are populated.

I was thinking to myself that an updateable view may take care of the problem. With a view, you can return data from multiple tables where the combination of data allows for more than the 8060 bytes per row. It would seem that making the view updateable is not completely straight forward, so I will leave that up to you (with a gently nudge).

http://www.informit.com/articles/article.asp?p=130855&seqNum=4&rl=1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

when you have an updateable view you can only update one table per statement (if I remember corrctly)
true I checked (If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.)


>>Here's what will eventually happen. 3 years from now, your customers will say... Your application isn't saving all of my data. I only typed 20 characters in to this field, but it's only saving the first 3 characters.

Won't happen, statement will fail and an error will be raised

normalize the DB, this will speed up the performance also since you will have more rows per page

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ddeegan]

Hello

From the thread I have learned

There is a small chance the user will not be able to insert data, if they populate all of the fields, they could receive an error message and the INSERT or UPDATE will fail.

I have the option of updating one table with a view.

Normilization will improve performance.

"...since you will have more rows per page" I will investigate this further so I understand it more.

Thanks to all for taking the time to answer my post.

Thanks
Dave

 

[SQLDenis]

>>"...since you will have more rows per page" I will investigate this further so I understand it more.

Let me explain
You have a book with 100 pages on each page you have a row
to search for a row you need to open up 100 pages

now if you had 20 rows per page you need to only open 5 pages

So the more rows per page/extent the less IO you have to perform to get the data
if you normalize your data you will have instead of a big book 2 smaller books and it will be faster to search through 2 small books that 1 big book

Of course this is very simplefied but you get the idea

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ddeegan]

Thanks Dennis - what is great about that example is that I can use it to explain to upper management. Priceless.

 

[SQLSister]

Rememeber that you can make a separate table that has aa one-to one relationship to store the additional fields in. Then you only need to query that table if the fields in it are part of what the user wants to see. And since you will have only one record relating to the orginal table, then you can still display it the way the users want to see it.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief