Mastering Dropping Columns in SQL Server

As an experienced database developer, I have helped redesign many SQL Server schemas over the years as requirements change. A key technique to keep databases lean and performant is properly dropping obsolete columns.

In this comprehensive guide, you‘ll learn:

• How to identify column dependencies to safely remove columns
• T-SQL scripts and SSMS methods to drop columns
• Advanced scenarios when dropping problematic columns
• Best practices to avoid issues and retain needed data
• Impacts on storage, performance, and applications

Whether you are a junior developer or seasoned DBA, this guide explores all facets around dropping SQL Server columns.

Examining Column Dependencies

Attempting to drop columns can fail if other objects reference them. Here are the main dependencies to check for:

Primary Key Columns

The table below shows an error trying to drop the primary key column ID:

ALTER TABLE Sales
DROP COLUMN ID;

Before dropping primary key columns like ID, first drop the primary key constraint:

ALTER TABLE Sales 
DROP CONSTRAINT PK_Sales;

ALTER TABLE Sales
DROP COLUMN ID;

Foreign Key Columns

Foreign key constrained columns also cannot be directly dropped. The foreign key referencing other table columns must be removed first:

ALTER TABLE Orders 
DROP COLUMN CustomerID;

Correct method:

ALTER TABLE Orders
DROP CONSTRAINT FK_Orders_Customers; 

ALTER TABLE Orders
DROP COLUMN CustomerID;

Indexed Columns

Dropping indexed columns fail as well:

ALTER TABLE Employees 
DROP COLUMN LastName;

Prior to dropping, remove the index instead:

DROP INDEX IX_Employees_LastName ON Employees;

ALTER TABLE Employees
DROP COLUMN LastName; 

Check Constraint Columns

Check constraints also prevent dropping columns. Disable the constraint first:

ALTER TABLE Inventory
DROP CONSTRAINT CK_Inventory;

ALTER TABLE Inventory
DROP COLUMN Quantity;

Dropping Columns via T-SQL

Now that we know how to check for blocking dependencies, next we cover the actual T-SQL code to drop columns.

Basic syntax to drop one column:

ALTER TABLE table_name
DROP COLUMN column_name; 

Example dropping two columns from a table:

ALTER TABLE Employees
DROP COLUMN MiddleInitial, OfficeNumber;

If no issues, SQL Server immediately removes the columns and updates system metadata.

Test Environment Example

Let‘s drop some columns from a test table #Temp:

SELECT * FROM #Temp;

ALTER TABLE #Temp
DROP COLUMN MiddleName;

SELECT * FROM #Temp; 

The MiddleName column no longer exists in the table after dropping it.

Using SSMS to Drop Columns

The SSMS GUI allows you to easily identify and drop columns as well.

Follow these steps:

1. In Object Explorer right click the table > Columns
2. Right click the target column > Delete
3. Confirm the column delete

For example, dropping the OfficeNumber and Phone columns:

This simplifies dropping columns without typing T-SQL directly.

Advanced Column Drop Scenarios

We will now go beyond basic column dropping to more complex examples you may encounter.

Mass Dropping Columns

If you need to remove a large set of obsolete columns after a major schema change, generate the script dynamically from system metadata rather than manually specifying each column.

This example deletes all columns except ID and Name:

DECLARE @sql NVARCHAR(MAX) = ‘‘;

SELECT @sql += ‘ALTER TABLE Employees DROP COLUMN ‘ + QUOTENAME(name) + ‘; ‘
FROM sys.columns
WHERE object_id = OBJECT_ID(‘Employees‘)
AND name NOT IN (‘ID‘, ‘Name‘);

EXEC sp_executesql @sql;

Thisconstructs a giant DROP COLUMN script stripped of the protected columns. Useful when removing many obsolete columns.

Columns with Default Constraints

If a column has a default value set, use DROP COLUMN WITH DEFAULT:

ALTER TABLE Inventory 
DROP COLUMN Quantity WITH DEFAULT;

Or alternatively disable the default constraint first:

ALTER TABLE Inventory
DROP CONSTRAINT DF_Inventory_Quantity 

ALTER TABLE Inventory
DROP COLUMN Quantity; 

Rebuilding Affected Indexes

After dropping varchar, nvarchar, varbinary, or sql_variant columns, associated indexes may have unused space from the removed columns.

Rebuilding indexes after dropping these column types helps reclaim wasted space:

ALTER INDEX ALL ON Employees
REBUILD;

Also enables key compression from the reduced column set in some cases.

Best Practices

When planning to drop production columns, best practices help avoid issues:

Archive Data First – Before altering schemas, extract dropped column values into separate archive tables in case the data becomes needed later. Uses simple joins if needed again.

Find Dependent Objects – Thoroughly check for dependent objects like indexes and constraints before attempting to drop columns.

Test Application Impact– Fully regression test applications to confirm they function properly without the removed columns.

Schedule Off-Peak – Only drop columns during minimal database usage to prevent locking issues.

Impact of Dropping Columns

Now that we have covered the mechanics of actually dropping columns, what is the fallout from removing columns?

Storage and Performance Optimization

Each column adds additional space requirements to the row size of a table. Fewer columns can reduce page splits and fragmentation by improving index key compression ratios.

Here is an example from production where unused columns were purged:

This freed up considerable space and I/O requirements.

After dropping many columns, also rebuild indexes to completely reclaim unused space.

Application Changes

Any downstream applications must be changed after altering underlying table structures:

• Applications accessing dropped columns will break
• Dependent views or stored procedures may need to be updated
• Additional application testing and changes take time and resources

Proper coordination ensuring continuity of service is vital for production systems. Allow time to regression test interfaces.

Conclusion

As an experienced database developer, I leverage column dropping to keep SQL Server databases lean and efficient as schemas evolve over years of new requirements.

Key takeaways include:

• Check dependencies like constraints before attempting to drop columns
• T-SQL and SSMS provide easy methods to remove columns
• Additional scenarios may require special handling like defaults
• Archive data first and test applications to prevent issues
• Rebuild indexes to recover unused space

Follow these best practices when dropping columns to optimize storage and avoid application downtime.

What techniques have you used to efficiently remove obsolete database columns?