You may want to specify an expected row size for a data-only-locked table when an application allows rows with null values or short variable-length character fields to be inserted, and these rows grow in length with subsequent updates. Set an expected row size to reduce row forwarding.
For example, the titles table in the pubs2 database has many varchar columns and columns that allow null values. The maximum row size for this table is 331 bytes, and the average row size (as reported by optdiag) is 184 bytes, but you can insert a row with less than 40 bytes, since many columns allow null values. In a data-only-locked table, inserting short rows and then updating them may result in row forwarding.
See “Data-only-locked heap tables”.
Set the expected row size for tables with variable-length columns, using:
exp_row_size parameter, in a create table statement.
sp_chgattribute, for an existing table.
A server-wide default value, using the configuration parameter default exp_row_size percent. This value is applied to all tables with variable-length columns, unless create table or sp_chgattribute is used to set a row size explicitly or to indicate that rows should be fully packed on data pages.
If you specify an expected row size value for an allpages-locked table, the value is stored in sysindexes, but the value is not applied during inserts and updates. If you later convert the table to data-only locking, Adaptive Server applies the exp_row_size during the conversion process and to all subsequent inserts and updates. The value for exp_row_size applies to the entire table.
