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)