Setting a default expected row size server-wide

default exp_row_size percent reserves a percentage of the page size to set aside for expanding updates. The default value, 5, sets aside 5% of the space available per data page for all data-only-locked tables that include variable-length columns. Since there are 2002 bytes available on data pages in data-only-locked tables, the default value sets aside 100 bytes for row expansion. This command sets the default value to 10%:

sp_configure "default exp_row_size percent", 10

Setting default exp_row_size percent to 0 means that no space is reserved for expanding updates for any tables where the expected row size is not explicitly set with create table or sp_chgattribute.

If an expected row size for a table is specified with create table or sp_chgattribute, that value takes precedence over the server-wide setting.