SPD Server Component File Types and Sizes

SPD Server uses four types of component files. Component files are physical file entities that SPD Server uses to track table and index metadata. When component files are combined, they form a logical structure that SPD Server understands and interprets as a single table. The following table lists the relative sizes of the four types of SPD Server component files.

File Type	File Extension	Relative Size	Number of Component Files
Table metadata	.mdf	Very small	1
Table data	.dpf	Large	1-to-many
Index	.idx	Medium-to-large	1 or more per index
Index	.hbx	Medium-to-large	1 or more per index

At a minimum, an SPD Server table consists of two component files, the metadata .mdf file and the data .dpf file. The size of the data file component depends on two factors: the size of a table column and the number of columns. The data .dpf component file can be many gigabytes in size. A single table can consist of many .dpf files. SPD Server is not constrained by an operating system file system size limit (such as the 2-gigabyte limit on file size that some UNIX systems impose).

The SPD Server index uses two index component files: the .hbx file and the .idx file. The .hbx file maintains a global view of the index and contains an entry for each key that exists in the index. The .idx file contains the row identifiers for each value in the .hbx file.

The size of the .hbx file depends on the cardinality of the index keys. The higher the cardinality of the index keys, the larger the .hbx file. The size of the .idx file is more difficult to determine because it depends on how the data is distributed.

The best-case scenario for creating an optimally sized .idx file occurs when the table is sorted by the indexed columns. The worst-case scenario for creating an optimally sized .idx file occurs when the index keys are distributed throughout the table.