Databases Reference
In-Depth Information
Figure 5-2
Employee Table
Storage
Customer Table
Storage
Row 0
(2 KB)
Row 0
(5 KB)
Row 1
(2 KB)
Row 2
(2 KB)
Row 3
(2 KB)
Page
Page
Data storage.
Take Microsoft SQL Server for example. The fundamental storage unit is a
page, which is 8 KB of contiguous space. A table row cannot span pages, but a
page can hold multiple rows, assuming that they are small enough.
Now, look in Figure 5-2. The maximum row size for the Employee table in
this example is 2 KB. That means that four rows fit on each page with no wasted
space. If you have 1000 rows in the table, you need 1000 times 2 KB, which is
2000 KB, or 2 MB to store the data.
You have a very different situation in the Customer table. The maximum row
size is 5 KB. That means you can fit only one row per page. What happens to
the remaining 3 KB? It's lost, wasted, and can't be used for any other purpose.
That means that each row effectively requires 8 KB. If you have 1000 rows, you
need 8 MB of storage.
When processing data in the Employee table, you need to work with 2 MB of
data (in this example) for each 1000 rows. However, performing an operation on
the Customer table means having to move around 8 MB of data. As you add rows
to the Customer table, the space needed increases, as does the wasted space. If you
just based your calculations on row size and didn't factor in page use, your space
requirement calculations would be off by nearly 40 percent for the Customer table.
Data volatility can directly impact response time and throughput. Some data,
such as active inventory records that reflect the changes in goods constantly being
put into and taken out of inventory, is updated frequently. Other data, such as his-
toric sales records, is seldom or never updated, except possibly for data from the
