Table Considerations Teradata

All relational database systems store rows in tables. As mentioned, a table is a two dimensional array that consists of rows and columns. Rows represent an entity within the database. A row consists of columns that permit the storage of values needed within the row.

Regarding the storage of rows, relational theory states that the order or sequence of rows and columns in a table should be arbitrary. In other words, the storage order does not matter for rows or the arrangement of columns within a row. You are, however, only allowed one row format per table. Although it makes no difference what sequence the columns are in, once you pick a format, all rows contain the same columns in the same sequence.

Columns within a row must have a unique name within the table and a data type. With these two pieces of information, the database can store, manage, retrieve and utilize the data. When it comes to character data, it may be fixed or variable in length. The nice thing about variable length character data is that it can save disk space.

Some database systems require that all fixed length columns appear at the front of the row and the variable length columns at the end. This is not true for Teradata. It automatically places variable length and null-able columns at the end of the row internally and on disk. However, for display purposes, all columns appear in the order in which they are defined in the CREATE TABLE statement. This is one less restriction to worry about with Teradata.

Maximum Columns per Table

A Teradata table may contain a maximum of 256 columns. For most database implementations, this limit is more columns then needed for a business application. Consider this, if each column is 1 byte long, they cannot be displayed as an entire row because the normal printer can only print 132 characters per line.

Originally, Teradata only allowed 50 columns during the creation of a table. Still today, I have seen sites that follow this old restriction. They create the table with 50 columns and then alter the table several times to reach the desired number of columns. If you find yourself changing old DDL and see this situation, simplify your life and everyone else's by doing it all in a single statement. Get it right the first time!

Teradata also has a limit of 512 columns over a table's lifetime. Therefore, if a table has 256 columns, the ALTER TABLE statement can be used to drop and replace up to 256 columns. Once you reach the 512-column limit you must create a new table to continue dropping and adding columns. If this happens, it proves that not enough planning went into the original design of the table. Try not to follow the philosophy of, "There is not enough time to do it right, but plenty of time to redo it later."


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

Teradata Topics