The size of individual table rows depends on the number and datatype of the columns in the table. For fixed-size datatypes, such as INTEGER and TIMESTAMP, the column is stored inline in the table record using the specified number of bytes. Table 4.1, “Memory Requirements For Tables By Datatype” specifies the length (in bytes) of fixed size datatypes.

For variable length datatypes, such as VARCHAR and VARBINARY, how the data is stored and, consequently, how much space it requires, depends on both the actual length of the data and the maximum possible length. If the maximum length is less than 64 bytes, the data is stored inline in the tuple as fixed-length data consuming the maximum number of bytes plus one for the length. So, for example, a VARCHAR(32 BYTES) column takes up 33 bytes, no matter how long the actual data is.

Note that VARCHAR columns can be declared in characters (the default) or bytes. For storage calculations, variable-length strings declared in characters are considered to consume 4 bytes for every character. In other words, a variable length string declared as VARCHAR(8) in characters consume the same amount of space as a string declared as VARCHAR(32 BYTES).

If the maximum length is 64 bytes or more, the data is stored in pooled memory rather than inline. To do this, there is an 8-byte pointer stored inline in the tuple, a 24-byte string reference object, and the space required to store the data itself in the pool. Within the pool, the data is stored as a 4-byte length, an 8-byte reverse pointer to the string reference object, and the data.

To complicate the calculation somewhat, data stored in pooled memory is not stored as arbitrary lengths. Instead, data is incremented to the smallest appropriate "pool size", where pool sizes are powers of 2 and intermediary values. In other words, pool sizes include 2, 4, 6 (2+4), 8, 12 (8+4), 16, 24 (8+16), 32 and so on up to a maximum of 1 megabyte for data plus 12 bytes for the pointer and length. For example, if the LastName column in the Customer table is defined as VARCHAR(32) (that is, a maximum length of 128 bytes) and the actual content is 95 bytes, the column consumes 160 bytes:

           8   Inline pointer
          24   String reference object
     4         Data length
     8         Reverse pointer
    95         Data
   107   128   Pool total / incremented to next pool size
         160   Total

Note that if a variable length column is defined with a maximum length greater than or equal to 64 bytes, it is not stored inline, even if the actual contents is less than 64 bytes. Variable length columns are stored inline only if the maximum length is less than 64 bytes.

Table 4.1, “Memory Requirements For Tables By Datatype” summarizes the memory requirements for each datatype.

For tables with variable length columns less than 64 bytes, memory usage can be sized very accurately using the preceding table. However, for tables with variable length columns greater than 64 bytes, sizing is approximate at best. Besides the variability introduced by pooling, any sizing calculation must be based on an estimate of the average length of data in the variable columns.

For the safest and most conservative estimates, you can use the maximum length when calculating variable length columns. If, on the other hand, there are many variable length columns or you know the data will vary widely, you can use an estimated average or 90th percentile figure, to avoid over-estimating memory consumption.

Indexes are sized in a way similar to tables, where the size and number of the index columns determine the size of the index. However, the calculations for tree and hash indexes are distinct.

For tree indexes, which are the default index type, you can calculate the size of the individual index entries by adding up the size for each column in the index plus 40 bytes for overhead (pointers and lengths). The size of the columns are identical to the sizes when sizing tables, as described in Table 4.1, “Memory Requirements For Tables By Datatype”, with the exception of non-inlined binary data. For variable length columns equal to or greater than 64 bytes in length, the index only contains an 8-byte pointer; the data itself is not replicated.

So, for example, the CustomerByName index on the Customer table, which is a tree index containing the VARCHAR(32) fields LastName and FirstName, has a length of 56 bytes for each entry:

           8   Pointer to LastName
           8   Pointer to FirstName
          40   Overhead
          56   Total

The following equation summarizes how to calculate the size of a tree index.

 (sum-of-column-sizes + 8 + 32) * rowcount 

For hash indexes, the total size of the index is not a direct multiple of individual index entries. Hash indexes are stored as a hash table, the size of which is determined both by the size of the content and the number of entries. The size of the individual entries are calculated by the size of the individual columns plus 32 bytes of overhead. (Hash indexes support integer columns only, so there are no variable length keys.) But in addition to the individual entries, the index requires additional space for the hash, based on the total number of entries. This space is calculated (in bytes) as two times the number of rows, plus one and multiplied by eight.

The following equation summarizes how to calculate the size of a hash index.

 (((2 * rowcount) + 1) * 8) + ((sum-of-column-sizes + 32) * rowcount) 

Note that hash indexes do not grow linearly. Incrementally allocating small blocks of memory is not productive. Instead, when the index needs more space (because additional entries have been added), the index doubles in size all at one time. Similarly, the index does not shrink immediately if entries are removed.

In general, the space allocated to hash indexes doubles in size whenever the index usage reaches 75% of capacity and the memory allocation is cut in half if usage drops below 15%.

Using the preceding formulas it is possible to size the sample flight database mentioned earlier. For example, it is possible to size the individual rows of the Flight table based on the schema columns and datatypes. The following table demonstrates the sizing of the Flight table.

The same calculations can be done for the other tables and indexes. When combined with the expected volumes (described in Section 4.1, “Planning for Database Capacity”), you get a estimate for the total memory required for storing the database content of approximately 500 megabytes, as shown in the following table.