The only thing in life we can consistently count on is change. This is especially true in a data warehouse environment. As business requirements change, sometimes it is necessary to reflect those changes into the tables. Teradata allows for modification of a table at either the table or column level using the ALTER command.
Here is a list of the table changes available to the ALTER TABLE:
You can ALTER a TABLE at both the column and table level. A table level change includes protection features or internal storage options such as FALLBACK, JOURNALING, FREESPACE PERCENT, etc. The column level allows you to change individual column attributes. For example you might be adding the TITLE, FORMAT, or another column level change.
The syntax for the ALTER statement is:
The following ALTER modifies the table to FALLBACK at the table level:
ALTER TABLE TomC.Employee, FALLBACK;
The Employee table in the TomC database now has a FALLBACK copy of each row. You can also remove FALLBACK, which is quick and easy. The system merely places the FALLBACK blocks of a table on the Free Cylinder List and the blocks are gone. This happens immediately. However, adding FALLBACK to a table is another story. This change may take a lot of time, depending on the number of rows in a table. When you add FALLBACK the system duplicates each row and places that row on another AMP in the same cluster. You are essentially doing a Full Table Scan (FTS) and a copy. If you have a million rows in the base table you are creating and distributing a million FALLBACK rows. The good news is that Teradata does this in parallel!
The following ALTER makes journaling changes at the table level:
ALTER TABLE TomC.Employee, NO BEFORE JOURNAL, DUAL AFTER JOURNAL;
The Employee table no longer has a BEFORE JOUNAL and now has a DUAL AFTER JOURNAL.
The following ALTER adds a TITLE of ‘School’ to one of the existing columns:
ALTER TABLE TomC.School
ADD School_Name TITLE 'School';
Although the above appears to be adding a column, the School_Name column already exists. When we explained Teradata to Sherlock Holmes he found another clue. Sherlock said, "Since the School_Name did not contain a data type, it could not possibly be adding a new column, but could only be altering an existing column." Sherlock was impressive. I went on to ask him what type of school he thought this table held. He said, "Elementary my dear".
You can make multiple changes to a table with one ALTER statement. The next request makes multiple changes to a table by adding multiple columns.
This example adds FALLBACK and two new columns (Soc_Sec and Dept_Name) to the table Employee:ALTER TABLE TomC.Employee, FALL BACKADD Soc_Sec INTEGER,ADD Dept_Name Char(20);
Notice in the above example that when adding new columns you must specify a data type.
The next request makes three changes to the table. The first is a journaling change at the table level. The second is at the column level and it drops the column called Soc_Sec. The third change is also at the column level. It adds a TITLE to the column called Dept_Name:
The next request changes the name of a column. The old column name was lname and the new column name is Last_Name.
ALTER TABLE TomC.Employee
Rename lname to Last_Name;
There are some restrictions when renaming columns, they are:
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.