Dropping a Table Teradata

The opposite of the CREATE TABLE command is the DROP TABLE. The DROP command deletes objects out of the Data Dictionary (DD) and the data rows inside the table are deleted from the system. Be CAREFUL!Gone is gone. So, make sure you are in the correct database!

The syntax to drop a table is:

DROP TABLE [<data-base-name>.]<table-name>
;

To ensure you are dropping the correct table you can also specify the database where the table resides by qualifying the table name with the database name.

To drop the table Employee in the database TomC you can use this command:

DROP TABLE TomC.Employee;

You can use two different SQL commands to accomplish the same thing. The first switches to the database TomC And the second drops the table.

DATABASE TomC; DROP TABLE Employee;

Because you can have the same table names in different databases it is important when performing the DROP function that you are sure you are getting rid of the table you want to drop.

Dropping a Table versus Deleting Rows

In most database systems, it is faster to drop a table than it is to delete of the rows. It is exactly the opposite in Teradata. As mentioned earlier, the delete of all rows of a table is the fastest thing that Teradata does. So, if you wish to get rid of all of the rows, use the DELETE command instead of the DROP command.

Furthermore, once a table is dropped it is no longer in the system. The data definition stored in the DD is gone. The access rights to the table are also deleted from the access rights table. There are times when a database administrator or user drops a table and creates the table again to fill it with fresh data. When doing this, the access rights of previous users need to be re-established.

This is why you drop a table when it is no longer needed, but DELETE the data rows from a table that you want to refresh with new data. A table loaded monthly with new data might fit this category. This allows the access rights to remain the same. The old data is deleted and the new data loaded. The access rights are unaffected because the data definition in the DDhas not changed and the access rights table is not affected.
Additionally, since the DROP requires locking the DD for WRITE to drop the table and delete the rows, a DELETE for just the data rows is faster. A DELETE of rows within a table is fast. It is much faster than a DROP because a DROP is actually deleting multiple rows from multiple DD tables.


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

Teradata Topics