Compression in Teradata

What is Compression in Teradata


Compression is the feature of Teradata which helps to reduce the disk storage. It is useful in reducing the disk space required by FIXED LENGTH columns or NULLS. It is a loss less compression so no data will be lost in it.

We can do compression on the following data types –

• Nulls, Zeros, Blanks. (Note all are different values and never consider them as same)
• Any numeric data type. (INTEGER, DECIMAL, FLOAT, DOUBLE, SMALL INT, BYTE INT)
• DATE
• CHARACTER(up to 255 characters)

Syntax of Compression –

CREATE TABLE EMP_ADDRESS(

    Address VARCHAR(50),

    City CHAR(20) COMPRESS (‘Bangalore’),

    StateCode CHAR(2)

  );

Guidelines of Compression –

• Columns must be FIXED LENGTH (not variable length) and it must be less than or equal to 255 characters.
• The compressed columns cannot be a part of PRIMARY INDEX .
• We can compress upto 256 constant values, which means for a single FIXED LENGTH column we can define max 256 values to be compressed.
• NULL will be compressed by default if you choose that column for compression.

NOTE – Maximum 256 values allowed in Teradata is including NULL, which means that you can define 255 unique values for that column (255 unique values + 1 NULL value = 256).

• Compression is case sensitive which mean if you compressed ‘TERADATATECH’ it will not compress ‘teradatatech’.

Difference between compression and VARCHAR

VARCHAR will be more efficient when the difference of maximum and average field length is high and compressibility is low.


Compression and fixed CHAR will be more efficient when the difference of maximum and average field length is low and compressibility is high.

Feel free to post your doubts as comment and if you like the post don’t forget to share 🙂