Data Types and Attributes in Teradata

Data Type

Type of data we are using inside a column Teradata supports both ANSI Complaint and Teradata extension and Teradata types.

ANSI Data type                                               Teradata Extension

Character  data type

Char()                                                                 Long Varchar()

Varchar()

Numeric data type

INT()

BYTE()                                                                Byte- INT()

int()                                                           VAR BYTE()

Float

Decimal(m,n)

Large objects

BLOB

CLOB                                                                  Graphic, VAR graphic

Other

N/A

Not applicable                                                Data, time stamp, period[TDB]

Interval etc

Note

For more data types description Refer to the teradata

Interested in mastering Teradata Training? Enroll now for FREE demo on Teradata Training.

Datatype Attributes

Title

Takes title for the column

Format

Takes formats of the column

Upper

Stores the data in upper case

Case specified

Stores the data in the specifies case

Default

Takes user define a default value.

Default user

Takes the session id of the user

Not null

Doesn’t allow null values etc

Column storage Attributes This attributes basically design to reduce the memory space

Compress  It is used for memory saving, it compresses Null and user-defined values to zero space

This Compression is possible for int, small int, byte int, float, date, double,  decimal, char etc

We can compress the below data type

a) Integer

b)byte int

c)small int

d)date

e)double

f)decimal

g)capital

h)Float/red

h)char

The below data types do not support

a)Varchar

b)Varbyte

c)var graphic

d)time

e)time stomp

f)Interval etc.

Syntax  Compress null or<User define value>

Example

1. Compress Null
2. Compress
3. Compress ‘Savings’

Learn more about Teradata Interview Questions in this blog post.

Multi-Value Compression

Supports multiple values compression(up to 255 column values & one null)

Syntax

Compress(‘Saving&’ ’Current’ )

Select*From DBC table size[Next take compress then insert the data and see the table size]

Create set table dum, fall back, No before journal, No after Journal, free space=2 percent, data block size =10201 bytes party id integer title ’corrpanyid ’

Party name varchar(30) upper case,

Party code integer default 77 compress 100,

J data  date format(‘yyyy-mm-dd’)

Sel* From dum

Insert into dum values(1,‘Vinay’,10,’2008-10-05’);

Insert into dum values(1,‘Vinay’,10,’2008-10-05’);

Duplicate error in terms of set

Insert into dum values(2,HDFC,’2008-10-05’);

Insert into dum values(3,HHH Vinay,’20,’2008-23-10’);

Failed because of the wrong date format.