You are currently viewing SQL Server To Teradata: Data Migration And Conversion Of Data Types From MS  – Full And Useful Guide In 5 Mins!
Could You Please Share This Post? I Appreciate It And Thank YOU! :) Have A Nice Day!
4.8
(1153)

Data migration between two different database systems always involves the conversion of data types [ SQL Server to Teradata ]. 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.

SQL Server To Teradata 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
Data migration and conversion of data types

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 in Teradata, 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)
Convert Data Types In Sql

That’s all about: SQL Server to Teradata: Data migration and conversion of data types from MS!

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!

BigData-ETL: image 7YOU MIGHT ALSO LIKE

How useful was this post?

Click on a star to rate it!

Average rating 4.8 / 5. Vote count: 1153

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?