Need to understand how Sybase 600 characters limitation works?

Posted on 2006-11-05
Last Modified: 2007-11-27
Hi All,

Can someone please tell me in layman term how the Sybase 600 characters limitation works?  

We are using Sybase ASE 12.5 on one of our project and due to the type of data that we track our data varies, we have records with very little info to it and also records that have lots of info.  We have run into some problems where sometimes our reports will run and sometimes we will get an error like: "Sybase 600 characters limitation reached ....".  I did some quick research and found that base on settings on the server Sybase has certain limitations, i.e. 600 for 2K page, 31 columns in group by/order by clause, etc....  What I need to understand is how the Sybase engine works in the background after I ran the query?  For example: if I have two records in the table:

Record Id     Desc
1                 50 characters
2                 700 characters.

If I run a query to select all from this table does the 600 characters limitation apply because of the second record or it doesn't because the first record only has 50 characters?  Also, is there a work around to this problem beside changing the page setting on the server.  TIA
Question by:Taz2
  • 5
  • 3
  • 2
LVL 19

Expert Comment

ID: 17877362
I will answer your last question first:  Nope.  Your stuck.  The only direct and easiest way around this is to change the page size to 4 or 8KB.  I would highly recommend you just bite the bullet and change the page size.

The rest of your question can be answered simply by saying that Sybase will give you an error when the total length of the fixed length fields, overhead, and variable length fields in a record approaches the page size, in your case, 2KB (the exact number escapes me right now but it is documented).

You could play some games like adding a second field to take overflow from the first field but that promises to be a pain in the butt unless your application is very, very simple.

The other way around this might be worse; that is to use CLOBs (Charater Large OBjects).  If you really have just a few hundred bytes in the field, you can define a CLOB field and do the simplest of operations against it in TSQL.  Depending upon what you are using for report generation, the tool may choke on the CLOB field altogether regardless of length.

Increasing the page size is definitely the right answer and is not hard to do.  You will have to rebuild the server and databases.  You will also have to BCP out any data you wish/need to salvage since you cannot backup/restore across page sizes.  Another option is to build the larger page size database on another machine and use CIS to connect to it and do INSERT/SELECTS to get the data moved.

Finally, the best argument for larger page sizes is the increase in performance.  If your row sizes are bumping against the 2KB limit, you really should be running an 8KB page size.  It reduces the number of physical I/Os in most cases which, of course, improves performance.

Best of luck,
LVL 10

Expert Comment

ID: 17882834
I'm going to disagree with Bill on this.  We could use more information on
the error and the TSQL causing it, but I think I know what it is and
how to get around it

I believe the limit you are hitting is for the maximum width of an index, which
is also the maximum length of a group-by (because ASE internally builds a worktable
with an index based on the group by columns to do that work).

A simple "select *" shouldn't hit the error at all

A way around this error is to have some shorter surrogate value for the long
column.  If that column contains only unique values, then you can just
add an identity column to the table and group by the identity column rather
than the long column.  If that colume contains duplicates, then an approach
is to create a lookup table that correlates a surrogate value with each unique
value (again, could be an identity column) and add a column to the base table
that would contain the shorter surrogate value.  Again, do your group-bys
using the surrogate value rather than the long one.

An insert/update trigger can be used to maintain the lookup table and the
value of the surrogate in the base table.

Going even further, you could drop the long column from the base table and
rewrite your code to get the long value through a join of the two tables.
If you have many long duplicate values, this could save quite a bit of space.
LVL 10

Expert Comment

ID: 17882869
Oh, yes - from DBCC SERVERLIMITS output:

Index-specific row-size limits

  Max index row-size, including row-overheads                       :
650     1300    2700    5400    
  Max user-visible index row-size                                   :
600     1250    2600    5300    

the values are for 2k, 4k, 8k and 16k page size servers
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

LVL 19

Expert Comment

ID: 17883319
Good catch, Bret.

I have to admit it never occured to me that anybody would try to index a 600 character field.

To paraphrase an old vaudville joke, "How do you get to Carnegie Hall?  Normalize, normalize, normalize" :-)


Author Comment

ID: 17883326
Hi Bret,

This is the error that we get:  

"Databae error: [Server (NameofServer)][Procedure DynamicSQL].  The current query would generate a key size of 750 for a work table.  This exceeds the maximum allowable limt of 600.  Contact your database supplier for more information"

It's random when and how we get this error, the only pattern that I can see is when we have lots of columns in our select clause or there are lots of summing that requires grouping.  My questions are two folds:
1. Does it make sense for me to go through each field/attribute in the table and decide (base on Data type/length) which combination of these field/attribute will generate this error and which will not?  For example:  if i have 3 fields in one table: Id (Int), Name Varchar(100), and Desc Varchar (255).  Would it make sense for me to say that if I select Id and Name then I won't run into the limitation but if I select Id and Desc then I will?  

2. How does the worktable in Sybase works?  is this the temporary workspace used when processing queries?  If the query is a long running query which takes up lots of space in the database and cause this type of error?

LVL 10

Expert Comment

ID: 17883413
1) well, you shouldn't hit the error in either of these cases because it just isn't something
you would hit on a simple select.  Even if there was a group by, no combination of those
three columns exceeds 600 bytes.   But if you had another varchar(255) column and grouped
by name, desc, and the other wide column, then you would.

There is a similar error for exceeding the maximum length of a row, which you can hit
if the sum of the sizes of the columns you are selecting exceeds the max length of a row
(which is a little under the server's page size, generally around 1964 bytes for an APL table).  
This is most commonly hit when selecting wide columns from multiple tables through a join.

2) Yes, worktables are system-generated temporary table.  The length of the query/transaction
won't have anything to do with this error - the important factor is the width of the columns
included in GROUP BY  (or CREATE INDEX).


Author Comment

ID: 17883660
Sorry, my first question wasn't clear.  Let me try again, let's say this is my table with two records as below:

Id (Int)        Project Name  Varchar(50)                          Project Desc  Varchar (255)                  Project Notes  Varchar (255)
1                Even though the data length is set at 50,       Actual length of data entered is 255      actual length of data entered is 255
                  actual length of data entered is 255

2               Actual length of data entered is 50                Actual length of data entered is 100       actual length of data entered is 100

So, let's say I ran a query SELECT * FROM table.  In theory I should get the two records above back, hence my questions:

1. Will I get anything back from my query or will I run into the 600 limitations?  If no, why not? the first record definitely is over 600 characters?  If yes, why?  the second record only has 252 characters?  

This is what I need to understand, how does the sybase works in this case?  does Sybase evaluate each record one by one and decides which one to return and which one to generate the error "This exceeds the maximum allowable limt of 600" ?  Thank you in advance for your help/patience in this, I have never work in Sybase before so this is a huge learning curve for me.
LVL 10

Accepted Solution

bret earned 100 total points
ID: 17884782
Your statement for the NAME column - even though the data length is set at 50, actual lenghth of data entered is 255
doesn't really make sense in this context.  If you try to insert 255 characters into a varchar(50), the value will be truncated down to the first 50 characters (silently, unless you have SET STRING_RTRUNCATION ON).  This will have happened at INSERT time; by the time any SELECT is done the data is certainly no more than 50 characters, so the maximum length of the data in this row is 560 characters.

You will get both rows back with a simple select *.  That is because the limit on the total size of a *row* is ~1964 bytes.

However, to answer your general question - in the case of this error, ASE will only raise it if there
is data involved in the query that hits the limit.  The error will be raised when the row that violates
the limit is processed.


Author Comment

ID: 17890743
Hi Bret,

Just more question regarding your statement here: "If you try to insert 255 characters into a varchar(50), the value will be truncated down to the first 50 characters....".  I thought that since Varchar is a variable length character data, storage size is the actual length of the data entered and not in n bytes?
LVL 10

Expert Comment

ID: 17891033
Yes, but only up the specified maximum length.  a varchar(20) will hold 1-20 bytes/characters.  a varchar(50) will hold 1-50, etc.
Anything over the declared maximum will be truncated.  And, oddly, empty strings "" are converted to a single space " ".


Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
As a business owner, there are many things that keep you up at night. Profit margins, employee retention, human resource protocols, whether your product or service will remain competitive. When you own or manage a technology company that operates la…
In a recent question ( here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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