SQL is the language we can interact with RDBMS. When referring to DB2 SQL, the following will give brief idea on DDL.
- CREATE
- ALTER
- DROP
- Truncate
Why CREATE…
The create statement creates a Table, which is also called database object.
The examples is…
CREATE MYTABLE
{
Invoice_id number notnull,
vendor_id number not null
};
To change a database object, then, you need to go for ALTER. What is ALTER. The example is as follows.
Example to Add new column:
ALTER TABLE MYTABLE ADD balance_due number(9,2);
The other word is DROP.
Why you need DROP…To delete a column or index from the existing table…you can use in your SQL Query.
Delete a column from MYTABLE:
ALTER TABLE MYTABLE DROP COLUMN vendor_id;
The TRUNCATE..
Why TRUNCATE you need, you already have DROP..
The reason is you can delete all the data from the Table, without deleting table columns.
Tip- When you issued TRUNCATE the indexes will not be affected. This is a good interview question.
Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters. Truncating has none of these effects.
Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.
The example for Truncate table.
TRUNCATE TABLE MYTABLE;
The example for DROP table.
DROP TABLE MYTABLE;
Srinimf 