The ALTER TABLE statement, along with commands like CREATE TABLE, belongs to the category of Data Definition Language (DDL) commands. It allows us to modify the definition of a table. Changes to an SQL Server table can include:

• Adding a new column
• Deleting a column
• Changing the data type or definition of a column
• Changing the name of a column

General Definition and syntax of ALTER TABLE in SQL Server

The syntax of the SQL DDL command typically looks like this:

• Always begins with ALTER TABLE
• Followed by what you want to do (ADD, ALTER, or DROP) and, in the case of a change, how the new data type should look

ALTER TABLE [table_name]
(ADD, ALTER COLUMN, DROP COLUMN) [column_name] new_definition;

An exception is renaming a column, which is done through a system procedure (as explained below). Now, let’s look at individual situations you might want to address.

ADD – Adding a Column to a Table using ALTER TABLE

Below is a command that adds a column named “ID” to the “Ja_jsem_tabulka” table, which cannot be empty.

ALTER TABLE [Database].[schema].[Ja_jsem_tabulka]
ADD [ID] INT NOT NULL;

ALTER COLUMN – Changing Column Definition using ALTER TABLE

Below is a script that changes an existing column named “ID” in the “Ja_jsem_tabulka” table, setting its data type to BIGINT and allowing it to be NULL.

ALTER TABLE [Database].[schema].[Ja_jsem_tabulka]
ALTER COLUMN [ID] BIGINT NULL;

DROP – Deleting a Column in a Table using ALTER TABLE

If you want to completely remove the “ID” column from the “Ja_jsem_tabulka” table, along with any potential consequences, you can use the following syntax. We do not need to define the column as in the previous cases.

ALTER TABLE [Database].[schema].[Ja_jsem_tabulka]
DROP COLUMN [ID];

sp_rename – Renaming a Column in a Table (System Procedure)

Renaming a column in SQL Server differs slightly from other platforms, as renaming can only be done through a special system procedure, sp_rename

sp_rename ‘schema.table.column_old’, ‘new_column’, ‘COLUMN’;

So, if you wanted to rename the “ID” column to “ID_NO” in the “Ja_jsem_tabulka” table, it would look like this:

sp_rename ‘dbo.Ja_jsem_tabulka.ID’, ‘ID_NO’, ‘COLUMN’;

Error Message – Saving changes is not permitted

You may have encountered this error message before. The explanation can be found in Microsoft’s technical documentation

Some DDL operations can be performed directly through the user interface of a SQL Server database management tool, such as SQL Management Studio. For example, adding a new column can often be done by editing the table directly, without the need for a script. However, certain changes, such as altering the data type of a column or changing its name, require DDL operations typically performed using T-SQL scripts.

This behavior is due to a default setting in the management studio, which checks whether you are attempting any actions that could result in the loss or modification of the original metadata or data in the table. Essentially, it serves as a safety measure for users.

For instance, if you were to change the data type from varchar(1000) to varchar(1), you would lose most of the data, as it would be truncated to a single character.

The SQL Management Studio, with its default Object Explorer UI settings, prevents such undesired actions. However, it still allows them through SQL scripts. The key prerequisites for performing such table operations in a production environment are understanding what you are doing and acting responsibly. If you are unsure, it’s best to test and back up before making changes in a production setting.

The described UI behavior can be disabled by:

• Opening Management Studio and clicking on Tools – Options
• Finding the “designers” option in the list of options
• Unchecking the checkbox for “Prevent saving changes that require table re-creation”