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.

SQL SERVER - Drop Multiple Columns from a Single Table multiple-delete-800x343

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

Solarwinds
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.

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

Solarwinds
,
Previous Post
SQL SERVER – SQL Agent – Job Start Whenever the CPUs Become Idle
Next Post
SQL SERVER – 7 Resources From Techorama Netherlands 2019

Related Posts

Leave a Reply

Menu