CREATE TABLE Teradata

The CREATE table statement establishes the name of the table, a name of each column in the table, its data type and any data attributes. It also defines the Primary Index for the table. In addition, you may specify data protection features, data attributes and constraints that dictate data quality.

The following is the syntax for using CREATE TABLE:

When you use the CREATE TABLE command Teradata uses this to define a table object in the Data Dictionary (DD) for the storage of rows. It inserts a row into the DBC.TVM (Table, View, Macro) table for the table name; one row per column is inserted into the DBC.TVFields table and a row for implicit user rights is inserted into the DBC.AccessRights table.

The following CREATE TABLE statement builds a table definition for a table called employee:

UNIQUE PRIMARY INDEX(emp);

The table created above is designed to show a simple example. The table now exists as a header on each AMP in the system. Teradata is now ready for you to load the data and run the queries. Lets discuss the above fundamentals in detail.
The table called employee is composed of six columns (emp, dept, lname, fname, salary, and hire_date). Each column is assigned a data type that defines and controls the internal representation of the data stored there. The emp and dept columns have a data type of INTEGER. An integer is a 4-byte column that uses signed binary to represent numbers ranging from -2,147,483,648 to 2,147,483,647.
The lname is the next column and it is defined as CHAR(20). There are two pieces here; The CHAR piece tells Teradata to expect character data and the (20) reflects the number of bytes to reserve for this column's data values. Character data stores letters, numbers, and special characters. The system will reserve 20 bytes to store this data no matter if the actual data entered is one character or 20. If the value entered takes up less than the full 20 spaces then Teradata will pad the remaining bytes with spaces. If someone has a name of ‘Coffing’ the system will internally store Coffing and 13 spaces.
The fname is defined as VARCHAR(20). This means that the fname is stored internally as a variable length character that could reach a maximum of 20 characters. In addition, a twobyte Variable Length Indicator (VLI) is stored to indicate the actual length of each value contained in this column. The name ‘Tom’ is stored as 3 characters and has a VLI indicating the length of this column is 3-bytes. Including the 2-byte VLI the internal storage is 5-bytes for the column holding ‘Tom’ and 6-bytes for the column holding ‘Mike’. Teradata handles the storage representation transparently to the user.
The salary column is defined as DECIMAL(10,2). This means that salary will represent a number that can have up to ten digits total with two of the digits being to the right of the decimal. Therefore, the largest value it can store is 99,999,999.99. Additionally, if you entered a salary of 75000 the system would see this as 75 thousand dollars. If you entered a salary of 85000.50 it would represent 85 thousand dollars and 50 cents. It always aligns on the decimal, whether literally present or assumed as .00 in the above value of 75000.
The last column in our example is named hire_date and this column represents a date. Teradata will internally store the date as an integer, but recognize that integer as a date.

Column Data Types

Teradata currently supports ANSI data types as well as Teradata extensions. The first chart below shows the ANSI standard types and the second chart is for the additional data types that are allowed as Teradata extensions.

Column Data Types

This chart indicates which datatypes that Teradata currently supports as Extensions:

This chart indicates which datatypes that Teradata currently supports as Extensions

The first chart indicates which data types that Teradata currently supports as ANSI Standards:

The second chart indicates which data types that Teradata currently supports as extensions:

The first example was designed to show the CREATE TABLE statement and a simple explanation of the column types. Teradata also allows you to:

  • Explicitly name the database where the table will reside
  • Create the table as a SET or MULTISET table
  • Define the protection methodologies such as FALLBACK or JOURNALING
  • Define the internal row storage in BLOCKS and FREESPACE
  • Further define column attributes
  • Define CONSTRAINTS
  • Define SECONDARY INDICES
  • Define DEFAULT VALUES

Each will be discussed in detail.

Specifying the Database in a CREATE TABLE Statement

Someone once said, "Life is like a beautiful melody, only the lyrics are messed up". Since we did not specify a database in the previous examples, the system defaults to the current database for the CREATE TABLE statement. Sometimes this is when the lyrics get messed up. To ensure your table is placed in the intended database it is a good idea to qualify the database name in the CREATE statement.

Here is the same example again, with one change. The employee table is created in the database called TomC.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

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

Teradata Topics