Data migration – conversion of data types from MS SQL Server to Teradata

Data migration – conversion of data types from MS SQL Server to Teradata

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

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 typesSize (byte)Range values
BYTEINT1-128 – +127
SMALLINT2-32768 – +32767
INTEGER4-2 147 483 648 – +2 147 483 647
BIGINT8-9 233 372 036 854 775 808 – +9 233 372 036 854 775 807
DECIMAL (m,n)1 – 16
NUMERIC1 – 16
FLOAT8
CHAR (x)variable1 – 64 000
VARCHAR (x)variable1 – 64 000
DATE4yyyymmdd
TIME6 or 8hh:mm:ss[.ssssss]
TIMESTAMP10 or 12yyyy-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:

  1. 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
  2. For numeric data, always check the smallest and largest attribute value
  3. 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
  4. Always use the smallest possible data type for flags of type 0 or 1 – BYTEINT
  5. 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 typesTeradata data typesComments
BITBYTEINT
TINYINTSMALLINTif all values are below 127, use BYTEINT
SMALLINTSMALLINT
INTEGERINTEGER
BIGINTBIGINT
DECIMALDECIMALif the precision is 0, eg DECIMAL (3,0) consider BYTEINT to BIGINT
NUMERICDECIMAL
MONEYDECIMAL
SMALLMONEYDECIMAL
FLOATDECIMAL
REALDECIMAL
DATEDATE
DATETIMETIMESTAMP
SMALLDATETIMETIMESTAMP
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!

Leave a Reply

avatar
  Subscribe  
Notify of