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 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 compress Null and user defined values to zero space
This Compression is possible for int, small int, byte int, flat, 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 does 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
- Compress Null
- Compress
- 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 wrong date format.
For Indepth knowledge on Teradata click on: