Alter Table

In SQL Server, the ALTER TABLE statement is used to add, modify, or delete columns in a table, or to change the table's constraints. Here are some examples of how the ALTER TABLE statement can be used:

To ADD COLUMN to an existing table in the SQL Server database.

ALTER TABLE table_name
ADD column_name data_type;

To modify existing column using ALTER COLUMN statement.

ALTER TABLE table_name
ALTER COLUMN column_name data_type;

To rename a column using RENAME COLUMN statement.

EXEC sp_rename 'Table.Old_Column', 'New_Column', 'COLUMN'

To delete a column using DROP COLUMN statement.

ALTER TABLE table_name
DROP COLUMN column_name;

To add a foreign key constraint using add a constraint statement.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

To drop a table constraint.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

It's important to note that altering a table will require an exclusive lock on the table, which means that no other queries can be executed on the table while the alteration is taking place. Also, In SQL Server, you can use the ALTER INDEX statement to rebuild or reorganize an existing index, or to disable or enable a clustered index.

ALTER INDEX index_name
ON table_name
REBUILD;

ALTER INDEX index_name
ON table_name
DISABLE;

It's important to test the performance and impact of any changes to a table or index before implementing them in a production environment.