DDL

DDL (Data Definition Language) is the part of SQL that creates, changes, and removes database objects like tables, indexes, and views. DDL changes the structure, not the data.

What is DDL

• DDL statements define the shape of the database: tables, columns, indexes, views.
• The three DDL commands are CREATE, ALTER, and DROP.
• DDL changes take effect immediately. There is no COMMIT or ROLLBACK for most DDL.
• Only users with the right privileges (usually DBAs) run DDL in production.

CREATE

• CREATE TABLE builds a new table with its columns, data types, and constraints.
• CREATE INDEX builds an index for fast access. CREATE VIEW builds a view over one or more tables.
• Always define the primary key in CREATE TABLE so every row can be uniquely identified.

ALTER

• ALTER TABLE changes an existing table: add a column, drop a column, or change a column definition.
• Adding a nullable column to a big table is quick. Changing a data type on a loaded table can take time.
• Some ALTER changes put the table in a pending state until REORG is run.

DROP

• DROP TABLE deletes the table and all its data permanently. Use it with great care.
• DROP INDEX removes an index. Queries still work, but may run slower.
• There is no UNDO for DROP. Take a backup or image copy before dropping objects in production.

Example

• Creating a table, adding a column to it, and dropping it:-