SAP ASE row size

[former_member596754]

How can we find row size of a table? update is failing since it reached to its max length limitation.

ASE 15.5 WSD 5.1

[former_member596754]

@Bart, We had calculated and reaching around 1684bytes but the update failed, definitely there will be some offset values how do we calculate?

don't you think is this a basic requirement? At a time we should be able to calculate a particular rows size!

for exa: what will be size for the following combination; You may be wondering by seeing the NULL on open_bal_200402 field but that is the reality.

CREATE TABLE dbo.akh

( acct_no char(12) NOT NULL,

acct_type char(3) NOT NULL,

open_bal_200402 decimal(21,6) NULL )

[jong-un_seo]

Hi Akhilesh,

Each row requires additional space to manage so we have to calculate overhead size too.
Please check the formula at the webpage.

https://help.sap.com/viewer/2693691eb79c472481cabe88ec2d9867/16.0.3.5/en-US/a9c64effbc2b1014b048f436...

Regards,
Jongun

[bart_van_kuijk]

It more than a limitation, it is a logical error to define a table that is allowed to be larger than the page size. I take you want to identify row(s) that are close to max size allowed.

ASE does not have a function to calcualte a row size , but you can do that by using datalength. You need to allow for some overheads on top of the value returned, so it not extact. It only gives you the lenght of each column, not the additonal structures needed to manage row size and variable length.

Presumably, you have large varchar columns. You could just have a look at those. Either way, this should give you a pretty good idea which rows are close to the max allowed.

Using the table DDL I used to mimic the problem:

create table length_check (id int , c2 varchar(2500) , c3 varchar(2500))
go

insert length_check values (1 , "row1" , "row1")
insert length_check values (2 , "longer string ..." , "really long string ......")
insert length_check values (4 , "too long to print here ..." , "too long to print here ......") -- not actual row inserted
go

You need to add up the length of each column in the table:

select id , datalength(id) + datalength(c2) + datalength(c3) as Row_Size from length_check
-- you can add where clause if you are looking for known rows

where id =

This results in
id Row_Size
----------- -----------
1 12
2 46
4 2906

Clearly row id 4 is the likely problem one.

This query will select all columns for the table from syscolums with the datalength function

select "datalength(" + name + ") + "
from syscolumns
where id = object_id("length_check")
go

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
datalength(id) +
datalength(c2) +
datalength(c3) +

You can convert that result in the query as above:

select id , datalength(id) +
datalength(c2) +
datalength(c3) as Row_Size from length_check
-- where id =

[bart_van_kuijk]

Hello Akhilesh.

Can you provide the exact error message seen? I suspect you get something like this, with 511 message :

Msg 511, Level 16, State 2:
Server 'ASE16', Line 1:
Attempt to update or insert row failed because resultant row of size 5008 bytes
is larger than the maximum size (4010 bytes) allowed for this table.
Command has been aborted.

The size will of course depend on the page size used. This example is based on a 4K page server, where a row can be max 4010 bytes.

You can check minlen & maxlen columns in sysindexes for sizes, but these hide some complexities. You will likely need to review your table definition.

Please confirm exact error message(s) seen and provide create table DDL.

Kind regards

Bart van Kuijk

[former_member596754]

Hi Bart, The server page size is 2k and the error was

Attempt to update or insert row failed because resultant row of size 1964 bytes is larger than the maximum size 1962 bytes allowed for this table.

I do understand it is a limitation; how can we check a particular row size!!!