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
2,822 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
  • 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
In  today’s increasingly digital world, managed service providers (MSPs) fight for their customers’ attention, looking for ways to make them stay and purchase more services. One way to encourage that behavior is to develop a dependable brand of prod…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now