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,892 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

861 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

24 Experts available now in Live!

Get 1:1 Help Now