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.
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).
This table contains 4 million rows, and the user executed the command to select all records from this table.
|
1 |
SELECT * FROM Products; |
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.
|
1 2 3 4 |
SELECT [ProductID] ,[ProductName] ,[ProductLaunchDate] FROM [SQLShackDemo].[dbo].[Products] |
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.
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.
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.
It opens a window and lists the column name to remove. Click Ok, and it removes the column from the table.
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.
It shows all column of a particular table.
Right-click on the left-hand side of a column and you get option Delete Column. Click on it to delete a column.
You can either save it by pressing CTRL + S or close the table designer and click Yes on the following window.
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.
|
1 2 3 4 |
USE [SQLShackDemo] GO ALTER TABLE [dbo].[Products] DROP COLUMN [ProductFeedback] GO |
- 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.
|
1 2 3 4 |
USE [SQLShackDemo] GO ALTER TABLE [dbo].[Products] DROP COLUMN [ProductFeedback], [ProductLaunchDate] GO |
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.
|
1 2 3 4 5 6 |
CREATE TABLE ProductSales (ID INT, ProductName VARCHAR(50), productPrice DEC(10, 2) NOT NULL CONSTRAINT ck_price_check CHECK(productPrice >= 0), ); |
We want to remove [ProductPrice] column, but this column has a CHECK constraint on it. Let’s try to remove it.
|
1 2 |
ALTER TABLE [dbo].[ProductSales] DROP COLUMN ProductPrice GO |
You get an error message that objects CK_Price_Check is dependent on the column [ProductPrice].
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.
|
1 2 3 4 |
ALTER TABLE [dbo].[ProductSales] DROP CONSTRAINT ck_price_check Go ALTER TABLE [dbo].[ProductSales] DROP COLUMN ProductPrice GO |
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.
It shows the CHECK constraints as per the following image.
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.
It gives you a validation warning message and gives a message about the check constraint.
Click on Yes, and it fails to modify the table. It cannot remove the [Productprice] column because CHECK constraint is dependent on it.
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.
|
1 2 3 |
Create View vw_ProductSales as SELECT * from ProductSales |
This view depends on all columns of the [ProductSales] table. Drop the column using the Alter table command.
|
1 2 |
ALTER TABLE [dbo].[ProductSales] DROP COLUMN ProductPrice GO |
We do not get any error message on the query execution. Let’s try to access the records from the view.
|
1 2 |
SELECT * FROM vw_ProductSales; |
We get an error message because the table does not contain the [ProductPrice] column. This column was available once we created the SQL view.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023