Specifying an expected row size for a data-only-locked table is useful when an application allows rows that contain null values or short variable-length character fields to be inserted, and these rows grow in length with subsequent updates. The major purpose of setting an expected row size is 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 it is possible to 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 can result in row forwarding.
See “Data-only locked heap tables” for more information.
You can 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 the table is later converted to data-only locking, the exp_row_size is applied during the conversion process, and to all subsequent inserts and updates.
