Estimating tables with variable-length rows

You can estimate the size of a table with variable-length rows with columns of the VARCHAR or NVARCHAR data type.

When a table contains one or more VARCHAR or NVARCHAR columns, its rows can have varying lengths. These varying lengths introduce uncertainty into the calculations. You must form an estimate of the typical size of each VARCHAR column, based on your understanding of the data, and use that value when you make the estimates.
Important: When the database server allocates space to rows of varying size, it considers a page to be full when no room exists for an additional row of the maximum size.
To estimate the size of a table with variable-length rows, you must make the following estimates and choose a value between them, based on your understanding of the data:
  • The maximum size of the table, which you calculate based on the maximum width allowed for all VARCHAR or NVARCHAR columns
  • The projected size of the table, which you calculate based on a typical width for each VARCHAR or NVARCHAR column

To estimate the maximum number of data pages:

  1. To calculate rowsize, add together the maximum values for all column widths.
  2. Use this value for rowsize and perform the calculations described in Estimating tables with fixed-length rows. The resulting value is called maxsize.

To estimate the projected number of data pages:

  1. To calculate rowsize, add together typical values for each of your variable-width columns. It is suggested that you use the most frequently occurring width within a column as the typical width for that column. If you do not have access to the data or do not want to tabulate widths, you might choose to use some fractional portion of the maximum width, such as 2/3 (.67).
  2. Use this value for rowsize and perform the calculations described in Estimating tables with fixed-length rows. The resulting value is called projsize.

Copyright© 2019 HCL Technologies Limited