Rajendra Gupta

Overview of the SQL DELETE Column from an existing table operation

September 6, 2019 by

In this article, we will explore the process of SQL Delete column from an existing table. We will also understand the impact of removing a column with defined constraints and objects on it.

Introduction

In a relational database, we use the schema design technique Normalizations and split the large tables into smaller tables. It helps to reduce the redundancy and dependency of data. We have multiple normalization forms specified in the following image.

Different forms of Normalizations

We do not cover normalization techniques in this article. You can refer to article – What is Database Normalization in SQL Server?

Suppose you have an existing table with multiple columns. There are specific columns in this table that are not relevant now. You do not require these columns. For example, consider a product table, and in this table, you have a column []. We implemented a separate mechanism to capture product feedback, and this column is not relevant now.

Let’s understand why it is essential to remove unwanted columns from the SQL table. Below is my table structure and we need to remove column having data type varchar(2000).

table structure

This table contains 4 million rows, and the user executed the command to select all records from this table.

Capture the actual execution plan (press CTRL + M) of this query along with client statistics (press Shift + Alt + S).

In another query window, execute the query without the [Productfeedback] column and capture the similar statistics.

Let’s compare the actual execution plan of both queries in SSMS. You can notice that query without the [Productfeedback] column provides improved performance. You see low values of compiled memory, CPU time, reduced wait counts.

The query optimizer and SQL database engine require to put an additional effort for this column while it is not useful to retrieve data from it.

Execution plan comparison

Similarly, compare the client statistics of both queries. You can note a significant reduction in the TDS packets received from the server. Bytes sent to the client, Bytes received from the server, client processing time and total processing time for the query that does not contain the [Productfeedback] column. It can help you to save the network bandwidth as well.

Comparison of client statistics

Here, I intend that you should perform database clean up and remove the unwanted columns to improve query performance as well.

Let’s explore how to remove a column from an existing SQL table.

SQL DELETE column using SSMS object explorer

We can remove a column using the Graphical User Interface (GUI) method in SQL Server. Connect to an instance in SSMS, expand databases. In the particular database, locate the particular table and expand the columns.

It shows all the columns of the particular table. Right-click on the column we want to remove and click on Delete as shown in the following image.

SQL DELETE column using SSMS object explorer

It opens a window and lists the column name to remove. Click Ok, and it removes the column from the table.

delete columns

SQL DELETE columns using SSMS table designer

We use the table designer in SSMS to define required columns, data types, and column properties such as primary key, identity columns. We can use it to delete a column from an existing table as well.

Right-click on the table and go to Design.

table designer

It shows all column of a particular table.

view all columns using table designer

Right-click on the left-hand side of a column and you get option Delete Column. Click on it to delete a column.

SQL Delete column

You can either save it by pressing CTRL + S or close the table designer and click Yes on the following window.

Confirmation message

SQL DELETE columns using the T-SQL table designer

We can use Alter table command to remove a column as well. The syntax is simple to use. The following command removes [ProductFeedback] column from the [Products] table.

  • First, specify the table name that contains the column to remove
  • Second, specify the name of the column that we want to delete

We can also remove multiple columns in a single Alter table command, but all columns should belong to a single table. This command removes [productFeedback] and [ProductlaunchDate] columns in a single command.

SQL DELETE column contains CHECK constraints

We use CHECK constraints to limit the value range in a column. Suppose you have a column having CHECK constrains on it.

We want to remove [ProductPrice] column, but this column has a CHECK constraint on it. Let’s try to remove it.

You get an error message that objects CK_Price_Check is dependent on the column [ProductPrice].

Error message

We cannot remove this column until it has a dependent object in it. To remove this column, first, remove the dependency and then delete the column.

Let’s try to remove column having CHECK constraint using table designer. Open table designer for the [ProductSales] table.

Right-click on the column and verify that CHECK constraints exist on this table.

CHECK Constraints

It shows the CHECK constraints as per the following image.

Verify constraint on existing column

Now, right-click on the column [ProductPrice] and delete column. It does not give any error message.

Close the designer window, and it asks you to click on Yes for saving changes.

Confirmation message

It gives you a validation warning message and gives a message about the check constraint.

Validation message

Click on Yes, and it fails to modify the table. It cannot remove the [Productprice] column because CHECK constraint is dependent on it.

Failed to drop column

We cannot remove the column using table designer if the column has dependencies on other objects.

SQL DELETE column having a SQL View defined

Let’s create a SQL View to select all records from [ProductSales] table.

This view depends on all columns of the [ProductSales] table. Drop the column using the Alter table command.

We do not get any error message on the query execution. Let’s try to access the records from the view.

We get an error message because the table does not contain the [ProductPrice] column. This column was available once we created the SQL view.

Dependent objects

  • Note: we should check object dependencies before dropping a column. It will eliminate the issues that we face later on.

Conclusion

In this article, we learned about SQL Delete a column from an existing table using different methods. We should be aware of all these methods. You should be careful in the production environment before removing columns. You should remove all dependencies and plan it properly.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
170 Views