Choosing a Data Type

Every column in a table must have a data type. The choice of data type is important for the following reasons:

Figure 22 shows a decision tree that summarizes the choices among built-in data types. The choices are explained in the following sections.

Figure 22. Choosing a Data Type

$begin figure description - If your data is boolean (true or false) then use BOOLEAN. If your data is made up only of integers then the type to use depends on the range of values needed. If all of the numbers are between negative two to the fifteenth minus one and two to the fifteenth minus one then use SMALLINT. If some of the values are outside that range but all are in the range of negative two to the thirty-first minus one and two to the thirty-first minus one then use INTEGER. If some of the values are outside that range but all are within the range of negative two to the sixty fourth minus one and two to the sixty fourth minus one then use INT8. If some of the values are outside that range then use DECIMAL(p,0). If not all of the values are integers and the number of fractional digits is fixed then use DECIMAL(p,s). If not all of the values are integers and the number of fractional digits is variable then use SMALLFLOAT, FLOAT, or DECIMAL(p) depending on the largest number of significant digits. If there are never more than 8 significant digits then use SMALLFLOAT. If there are sometimes more than 8 but never more than 16 significant digits use FLOAT. If there are ever more than 16 significant digits then use DECIMAL(p). If your data is chronological and the data represents a span of time rather than a specific point in time use INTERVAL If your data is chronological and represents a point in time then use either DATE or DATETIME. Use DATE if the data is precise to the day; otherwise use DATETIME. If your data contains non-English characters use either NCHAR(n) or NVARCHAR(m,r). Use NCHAR(n) if there is no or little variance in item lengths; otherwise use NVARCHAR(m,r). If your data is made up of ASCII characters and there is little or no variance in the lengths of the items then use either CHAR(n), CLOB, or TEXT. Use CHAR(n) if none of the items are over 32 766 bytes in length; otherwise use CLOB if you need random access to the data (reading or writing to any portion of it) or TEXT if you do not. If your data is made up of ASCII characters but there is significant variance in the sizes of the items then use LVARCHAR if any of the lengths will exceed 255 bytes;otherwise use VARCHAR(m,r) or its synonym CHARACTER VARYING(m,r). If some part of the data is not ASCII then use BLOB if you need to have random access to the data;otherwise use BYTE. - end figure description$

begin figure description - Refer to the description for the previous figure. - end figure description