Section 2.5. Stable Database Schema

2.5. Stable Database Schema

The use of data definition language (DDL) to create, alter, or drop database objects inside an application is a very bad practice that in most cases should be banned. There is no reason to dynamically create, alter, or drop objects, with the possible exception of partitionswhich I describe in Chapter 5--and temporary tables that are known to the DBMS to be temporary tables. (We shall also meet another major exception to this rule in Chapter 10.)

The use of DDL is fundamentally based on the core database data dictionary. Since this dictionary is also central to all database operations, any activity on it introduces global locks that can have massive performance consequences. The only acceptable DDL operation is truncate table, which is a very fast way of emptying a table of all rows (without the protection of rollback recovery, remember!).

Creating, altering, or dropping database objects belong to application design, not to regular operations.