SQL Alter Table: Top Query Examples

With the ALTER statement, you can add columns, add indexes, add keys, add buffer pools, and modify function parameters. Here are the SQL queries on how to use ALTER.

SQL ALTER statement: These You Can do

Here are database objects you can ALTER. The list is TABLE, INDEX, FUNCTION, MASK, TABLESPACE, SERVER, SEQUENCE, and THRESHOLD.

1. To Add a Column

Here you can add new column for an existing Table. Also, you can modify the existing column data type.

ALTER TABLE table_name ADD COLUMN name_of_column;

2. To Add Index

You cannot add new Index on existing Table. When you want to add new index, you need to drop existing ones and to create new ones.

The REGENERATE specifies that the index will be regenerated or rebuild. This command is useful if an index already created.

ALTER INDEX index_name REGENERATE;

3. You Can ADD Buffer pools

You can modify database parameters. For instance, you can add more buffer-pools. Also, you can add storage or drop storage.

ALTER DATABASE database_name new_buffer_pool;

4. You Can ALTER Criteria of a Function

You can modify the parameters (criteria) of an user defined function.

ALTER FUNCTION GET_TABLE
    RESTRICT CARDINALITY 10000;

Summary

Usually, in production, the ALTER statement Admin will use it. But in the test regions, a developer can use ALTER statement.

References

• More topics on SQL ALTER uses

Related Posts

• How to Count and Print Blank Spaces in Python
• The Easy Way to Get Duplicate Records: Top SQL Query
• How to write Stored Procedure in DB2: Unique Ideas