Solved

Need to understand the Warning: Row size (2494 bytes) could exceed row size limit, which is 1964 bytes.

Posted on 2008-06-10
7
3,316 Views
Last Modified: 2012-05-12
Hi All,
Iam using Sybase ASE 12.5 .While change the structure of the below sybase table using alter scripts I am getting the warning message "Row size (4494 bytes) could exceed row size limit, which is 1964 bytes."

Existing table : BDS_TEMP_ENROLL

GRGR_ID                        char                                     8        NULL
SGSG_ID                        char                                     4        NULL
SBSB_ID                        char                                     9        NULL
MEME_SFX                       char                                     2        NULL
FIRST_NAME                     char                                    15        NULL
D_CERT_T1                 varchar                               70        NULL
D_CERT_T2                 varchar                               70        NULL
D_CERT_T3                 varchar                               70        NULL
D_CERT_T4            varchar                               70        NULL
PDS_DESC                      char                               70        NULL  


Structure of New  table : BDS_TEMP_ENROLL (After running ALTER scripts)
 
GRGR_ID                        char                                     8        NULL
SGSG_ID                        char                                     4        NULL
SBSB_ID                        char                                     9        NULL
MEME_SFX                       char                                     2        NULL
FIRST_NAME                     char                                    15        NULL
D_CERT_T1                 varchar                               1000        NULL
D_CERT_T2                 varchar                               1000        NULL
D_CERT_T3                 varchar                               1000        NULL
D_CERT_T4            varchar                               1000        NULL
PDS_DESC                      char                               70        NULL  

Even though the structure of the table is changed with the alter scripts, I am worried about the warning msg. I am pretty sure that fields D_CERT_T1,D_CERT_T2,D_CERT_T3,D_CERT_T4 will never use the 1000 char limit defined but I want to still have this defined with 1000 char. Can I ignore this warning msg? Please advice.


Thanks
0
Comment
Question by:maddyforums
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 10

Expert Comment

by:bret
ID: 21754700
Yes, you can ignore the message.  It is just a warning.
Your data is limited to the actual row size limit.  However, the multiple long variable length columns give you flexibility over which columns contain the data (up to that limit).  The warning is just saying that if you tried to put in 1000 bytes of data into each of the D_ columns, all that data wouldn't fit and you would get a hard error at the time of the insert attempt.
0
 

Author Comment

by:maddyforums
ID: 21754762
Thanks Bret, what is that I should do to avoid this warning msg ? Do I have to change any setting on the Sybase server?
If by any chance in the worst case all the D_columns have long data and the total length of the row exceeds the 1964 bytes limit, will I get a error while inserting the record ? Is there any way workaround for this ? Please advice.

Thanks

0
 
LVL 19

Expert Comment

by:grant300
ID: 21755011
Maddy,

Bret pretty well covered it and earned the points on this one.  If I remember correctly, he answered the 600 byte limit question as well.  I just wanted to add my two cents worth.

Yes, if you attempt to stuff too much information into the row, you will indeed get an error on insert.

You might want to consider going to a larger page size at some point.  You have to rebuild the server so it is not something you can do any old time but there are advantages.

In addition to giving you the ability to store larger rows such as in the situation you outlined above, you will find you get better performance.  The performance bump comes from reduce I/O.  Disk drives now all do I/O in bigger chunks than 2K at a time, usually at least 8 or 16K and, in some RAID hardware and SANs a fundamental I/O can be as large as 2Megabytes !!!

I routinely install servers using direct attached disk and/or embedded RAID controllers with 8KB pages as that seems to be the sweet spot.

Finally, you have a kind of non-relational schema design there.  Not only are you exposed because the columns are getting longer, you may well be in trouble when the come back and want to add D_CERT_T5 and D_CERT_T6 columns.  Not knowing the application space it may be that T1-T4 are related to some physical construct that can never change but you have a mess if not.

You can solve both issues by normalizing the D_CERT_Tx fields out into another table.  It is a pain if you have an application already in production but you will have a lot more flexibility going forward.

Best of luck,
Bill
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 10

Expert Comment

by:bret
ID: 21755128
You cant really avoid getting the warning, but you only get it when you create or alter the table (just once each time).

As Grant mentions, you can have rows wider than 1964 bytes if your server is configured for a larger page size - your options are 2k, 4k, 8k, or 16k pages, and the maximum row size is a little smaller than the page size.  Run "dbcc traceon(3604) dbcc serverlimits "  to get the exact details.

The page size can only be specified at the time you build the server.  If you want to change it, you need to build a new server and transfer (bcp) the data over.  You can't use dump and load.  You could use sybmigrate.
0
 

Author Comment

by:maddyforums
ID: 21755177
Thanks a lot Bill for your explanation, Can I consider the below as the limitation in sybase 12.5 ?
"Row size in a table shouldn't exceed row size limit, which is 1964 bytes"

Thanks
Maddy
0
 
LVL 10

Accepted Solution

by:
bret earned 125 total points
ID: 21755198
Well, it is more "row size can't exceed row size limit, which depends on the page size and lock scheme"
see dbcc serverlimits for the exact limits.
0
 

Author Closing Comment

by:maddyforums
ID: 31465914
Thanks a lot Bill & Bret. Its great to have experts like you around.
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
OnPage enhanced its integration with ConnectWise Manage to offer incident responders more control over the ticket and Incident Resolution Lifecycle.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question