ALTER TABLE Statement in SQL Server(TSQL)

We can perform the following operations with ALTER TABLE Statement.

  • Add a column to a table
  • Delete a column from a table
  • Change the data type of a column
  • Increase or decrease the size of a column
  • Switch Partition
  • Enable or disable Constraints or triggers

In this article I will describe the above items through an example. In order to use our examples, we create a table as below and add a few records.

Add a column to the table:

We are adding a column in the varchar (10) data type with the help of the following script. After adding the column, we see that when we read the table with SELECT, the values of the column that we added are returned as NULL for existing records.

Drop a column from the table:

We drop the column we added with the help of the following script.

Change the column’s data type:

Changing the column’s data type is a bit more complicated. If we change the data type of the column, the values in the column must match the new data type.

With the following script, we convert the data type of the Name column from varchar (10) to nvarchar (10).

When there are text data in the column, if we try to change the data type to int, we will get the error as follows.

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the nvarchar value ‘Nurullah’ to data type int.

The statement has been terminated.

Increase or decrease the column’s size:

Set column size as a larger value:

Let’s increase the size of the column without changing the data type. We can do this quickly without any interruption.(Even if the table is too large)

Set column size as a smaller value:

This process is a little more complicated. Because the column’s current data may not allow it. Let’s go back to our example. There are two records in the table “dbo.Personel” (Nurullah and Faruk). Let’s try to set the data type of this column as varchar (5).

Since the largest data in the table is 8 characters, the smallest value we can set is varchar (8). If we try to set as a smaller value, we get the error as follows.

Msg 8152, Level 16, State 2, Line 1

String or binary data would be truncated.

The statement has been terminated.

You can use the following script to calculate the maximum data length of the column in the table.

Switch Partition:

You can find details in the article “Sliding Window-Switch Partition-Split Range-Merge Range“.

To enable or disable Constraint or Triggers:

With ALTER Table we can add and delete constraint. You can find details in the article “How To Create CHECK CONSTRAINT“. You may want to read the article “SQL Server Trigger Types“.

With the following script, we disable the trigger named myTrigger in the table.

With the following script, we enable the trigger named myTrigger in the table.

With the following script, we disable the constraint named myConstraint in the table.

With the following script, we enable the constraint named myConstraint in the table.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *