SQL SERVER – Drop Multiple Columns from a Single Table

Recently, while working with a client, we determined that they have many unused indexes and unused columns in their table. I worked along with them doing a Comprehensive Database Performance Health Check. I noticed that DBA was dropping the unused columns one at a time, which was taking a lot of time for it. I was surprised that he did not know the trick to Drop Multiple Columns from a Single Table. Let us learn about that in today’s blog post.

First, let us create a test table with multiple columns in a temporary database.

USE tempdb
GO
CREATE TABLE MyBigTable (ID INT, Col1 INT, Col2 INT, Col3 INT)
GO

Now you can run the following query to drop columns one at a time.

ALTER TABLE MyBigTable
DROP COLUMN Col1
GO
ALTER TABLE MyBigTable
DROP COLUMN ID
GO

However, instead of dropping one column at a time, the better way to do is to drop multiple columns in a single statement. To my surprise, not many people know this simple trick. Let us see it here.

ALTER TABLE MyBigTable
DROP COLUMN ID, Col1
GO

Do let me know if you know any such simple trick which you believe many people should know but not yet very famous. I would be happy to post the same with due credit to you.

Here are six-part blog post series I have written based on my last 10 years of experience helping with Comprehensive Database Performance Health Check. I strongly recommend you to read them as they walk you through my business model.

• Consulting 101 – Why Do I Never Take Control of Computers Remotely?
• Consulting 102 – Why Do I Give 100% Guarantee of My Services?
• Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?
• Consulting 104 – Why Do I Give All of the Performance-Tuning Scripts to My Customers?
• Consulting 105 – Why Don’t I Want My Customers to Return Because of the Same Problem?
• Consulting Wrap Up – What Next and How to Get Started

Reference: Pinal Dave (https://blog.sqlauthority.com)