Data migration between two different database systems always involves the conversion of data types. In this post, I will introduce the concept of data migration and show how to convert data types from Microsoft SQL Server to Teradata database types.
Data migration is one-time transfer of data from one IT system, database to another repository. Usually it is a one-way trip, involves many departments of the company and entails the termination of the existing database system.
Migrations of data sets to new databases are always the result of requirements for current IT systems. The change is to ensure better query performance, easier system scalability, and more efficient management of data sets.
Teradata data types
Each table attribute is associated with a specific data type that determines what values will be stored in it.
The table below presents the most common data types in the Teradata database along with their size and range of values:
|Data types||Size (byte)||Range values|
|BYTEINT||1||-128 – +127|
|SMALLINT||2||-32768 – +32767|
|INTEGER||4||-2 147 483 648 – +2 147 483 647|
|BIGINT||8||-9 233 372 036 854 775 808 – +9 233 372 036 854 775 807|
|DECIMAL (m,n)||1 – 16|
|NUMERIC||1 – 16|
|CHAR (x)||variable||1 – 64 000|
|VARCHAR (x)||variable||1 – 64 000|
|TIME||6 or 8||hh:mm:ss[.ssssss]|
|TIMESTAMP||10 or 12||yyyy-mm-dd hh:mm:ss|
From SQL Server to Teradata
Data types in IT systems practically are always differ – some are more detailed allowing for more precise specification of the attribute as MONEY, and others more general as DECIMAL. The most important thing is to choose the right data type with the appropriate size.
When converting data types between databases, always remember:
- For alphanumeric data, always check the maximum field length and try to choose the length taking into account the data that will appear in the future
- For numeric data, always check the smallest and largest attribute value
- If the precision (n) for the DECIMAL attribute is 0 consider using BYTEINT, SMALLINT, INTEGER or BIGINT depending on the maximum values of the attribute
- Always use the smallest possible data type for flags of type 0 or 1 – BYTEINT
- When specifying the data type for the same attribute in different tables, always use the same data type and the same precision (especially if the attribute is used in table joins)
The following table shows the conversion of data types for the most common SQL Server database types:
|SQL Server data types||Teradata data types||Comments|
|TINYINT||SMALLINT||if all values are below 127, use BYTEINT|
|DECIMAL||DECIMAL||if the precision is 0, eg DECIMAL (3,0) consider BYTEINT to BIGINT|
|CHAR (x)||CHAR (x)|
|VARCHAR (x)||VARCHAR (x)|
If you enjoyed this post please add the comment below or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!