Column Attributes Teradata

Alfred North Whitehead once said, "We think in generalities, but we live in details". We have seen examples in this chapter of creating tables with simple column definitions. These examples have been generalities, but often we need to model our columns in great detail. It is time to take the next step. When defining a table it is normally advantageous to be more specific regarding the definition of the columns and their attributes.

Column Attributes

The next CREATE TABLE builds a table definition with attributes.

In the above example the columns have been further defined using column attributes. The dept column is defined with the NOT NULL attribute. This means that a NULL value cannot be stored in the dept column.
The lname column has been further defined to state that for comparison purposes, the data is not casespecific. This means that it does not matter if the data is in upper case, lower case, or a combination.
The fname column has a TITLE associated with it. So, whenever it is selected, ‘FIRST NAME’ appears as the report heading instead of the column name.
The salary column is automatically formatted as currency in the output when selected using BTEQ.
The hire_date is also formatted. It displays as the alpha month followed by the numeric day and 4-digit year.

Lastly, the Byte_Col column is added. It is a byte column with a length of 10. The example shows how to initialize it with a value of zero. It is provided as a hexadecimal number using the xb designation. This designation would also be a valid comparison for retrieval of the row. Notice the word compress.

This compress does not mean we are going to use WINZIP to shrink the column. Compress allows you to take one specific value and store the default in the table header. Let me explain. Lets say the Dept of Motor Vehicles in California has a database that tracks all people with a California driver's license. Around 99.9% of the drivers would have California as their state code on their address. Instead of storing "CALIFORNIA" in millions of records the compress will store the value "CALIFORNIA" in the table header. Now, a value can be assumed in a row as a default unless another value exists inside the column.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics