Data Type Considerations | Database Design | Teradata Vantage - 17.10 - Data Type Considerations - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

Different column data types occupy different amounts of disk space. This topic examines the various Teradata data types and indicates their absolute sizes.

The information presented here is for sizing purposes only. For specific usage information about the various data types supported by Teradata, see Teradata Vantageā„¢ - Data Types and Literals, B035-1143.

Data Types And Hashing

The data types for the primary index column set also have an important effect on how rows hash. For example, a primary index value typed DECIMAL with one precision generally hashes to a different AMP than the same primary index value typed DECIMAL with a different precision.

Data Type Size Differences For Packed64 and Aligned Row Format Architectures

Several data types have different sizes depending on whether the rows of a system are formatted using a packed64 or an aligned row format. The row size increases for aligned row formats are not all due to increased data type sizes. Byte alignment issues also play a significant role in this increase. Furthermore, neither study examined the effects of the increased data block header size introduced by WAL on storage (see Byte Alignment for more information).

The following table lists the predefined data types that have different disk storage sizes for packed64 and aligned row formats. If a data type is not listed in the table, then its allocated size is identical for packed64 format and aligned row format systems.

Data Type Packed64 Format Size (bytes) Aligned Row      Format Size          (bytes) Allocated         Aligned      Format Size          (bytes)
TIME               6              4                8
TIMESTAMP             10              4              12
INTERVAL DAY TO SECOND             10              4              12
INTERVAL MINUTE TO SECOND               6              4                8
INTERVAL SECOND               6              4                8