Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Row Size Limitation

3 posts in Windows NT Last posting was on 2000-04-25 15:28:15.0Z
Chieng Posted on 2000-04-24 01:32:13.0Z
From: "Chieng" <ctt72@pc.jaring.my>
Subject: Row Size Limitation
Date: Mon, 24 Apr 2000 09:32:13 +0800
Lines: 24
X-Newsreader: Microsoft Outlook Express 4.72.3110.1
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <OevOy1Yr$GA.212@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: j44.kch8.jaring.my 161.142.221.114
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2270
Article PK: 1089702

To all the ASE 11.9.2 gurus out there!

I have encountered this warning message while creating a new table:

Server Message: Number 1708, Severity 10
Line 10:
Warning: Row size could exceed row size limit, which is 1964 bytes.

My question is:

Is there somewhere in the settings which I can set to increase this row size
limitation?
If not, how would you design your table and yet you do not compromise the
columns/data ?

Any advise would be greatly appreciated and thank you.


Evelyn
USS


Andy Price Posted on 2000-04-25 15:28:15.0Z
From: "Andy Price" <andy.price@canada.com>
References: <OevOy1Yr$GA.212@forums.sybase.com>
Subject: Re: Row Size Limitation
Date: Tue, 25 Apr 2000 17:28:15 +0200
Lines: 54
Organization: Paris, France
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <KdwHzvsr$GA.271@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 213.167.0.3
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2261
Article PK: 1089693

Evelyn,

Sybase uses 2k pages, and part of this is used for the row header. If you
have a row longer than 1964 bytes, you should be asking yourself why the row
is so long. Are you using lots of varchar columns, for instance?

Be careful when changing locking schemes as well, as this impacts the row
limits:

Allpages locking has a 32 byte overhead
Data only locking has a 46 byte overhead

Remember that varchars, for instance, require extra bytes to control the
length of the field, so this will reduce the space available for each row.

Row length calculation:
For a row with variable length columns, there is an 8 byte overhead, plus
the total of all fixed-length columns, plus total of all variable length
columns, plus number of variable length columns * 2.

Andy Price,
Sybase Certified Adaptive Server DBA v11.5
email: andy.price@canada.com

Chieng <ctt72@pc.jaring.my> wrote in message
news:OevOy1Yr$GA.212@forums.sybase.com...
> To all the ASE 11.9.2 gurus out there!
>
> I have encountered this warning message while creating a new table:
>
> Server Message: Number 1708, Severity 10
> Line 10:
> Warning: Row size could exceed row size limit, which is 1964 bytes.
>
> My question is:
>
> Is there somewhere in the settings which I can set to increase this row
size
> limitation?
> If not, how would you design your table and yet you do not compromise the
> columns/data ?
>
> Any advise would be greatly appreciated and thank you.
>
>
> Evelyn
> USS
>
>
>
>


"Mark A. Parsons" <pegasys Posted on 2000-04-24 02:32:00.0Z
Message-ID: <3903B220.60265A92@_internet.co.nz>
Date: Mon, 24 Apr 2000 14:32:00 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys Ltd
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Row Size Limitation
References: <OevOy1Yr$GA.212@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 35
NNTP-Posting-Host: p337.ipa1-n8-16.iconz.net.nz 210.48.25.81
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2269
Article PK: 1089701


> Is there somewhere in the settings which I can set to increase this row size
> limitation?

Nope. Sybase is (currently) limited by it's page size of 2K ...
subtract out a few bytes for the page header, offset table, and row
header ... and you're left with a max data row size of 1964 bytes.

> If not, how would you design your table and yet you do not compromise the
> columns/data ?

A couple ideas/suggestions ...

1) The text and image data types are not limited to 1964 bytes; this
accomplished by giving them their own linked list of 2K pages ... with a
pointer stored in the actual data row. Problems with text/image include
not being able to pass them as arguments to stored procs and not having
many ways of manipulating them within T-SQL.

2) If you're finding that your tables have too many columns ... split
some of the columns out into another table ... just drag along the same
primary key as in the original/sibling table. Problems here include ...
making modifications to a data row in both tables require the use of a
transaction wrapper in order to insure the modifications are done as a
single unit of work ... and having to do 2 select's (or a 2-table join)
to pull out info from both tables.


--
Mark A. Parsons

Iron Horse, Inc. iron_horse@compuserve.com
Pegasys (200), Ltd pegasys@internet.co.nz