Alter Table Drop Column

Dropping a column is a fairly straightforward operation as long as no dependencies depend on the column. The following code tested on SQL 2008 returns back almost instantly even when tested against tables with over 1 million rows. It seems the drop column statement performs similar to a truncate statement in that it simply moves a pointer and does not actually delete the data from the pages.

It is highly recommended you test on a separate environment before deploying these changes to your production system.

The syntax is simple, we can pass in a comma delimited list of columns to drop from the table.

[cc lang=”sql”]
ALTER TABLE ##my_tables
DROP COLUMN first_name, last_name
[/cc]

For the first example, we’ll create a table and then drop one of it’s columns.

[cc lang=”sql”]

IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL
BEGIN
DROP TABLE ##meme
END

CREATE TABLE ##meme
(
first_name VARCHAR(50),
last_name VARCHAR(50),
ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE
)

ALTER TABLE ##meme
DROP COLUMN first_name
[/cc]

That was easy, it simply dropped the column. It won’t always happen that way however. If we have a constraint, index, or key that depends on the column, we need to drop or disable that first.

[cc lang=”sql”]
IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL
BEGIN
DROP TABLE ##meme
END

CREATE TABLE ##meme
(
first_name VARCHAR(50),
last_name VARCHAR(50),
ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE
)

ALTER TABLE ##meme
DROP COLUMN first_name, ssn
[/cc]

Here we get the error:

Msg 5074, Level 16, State 1, Line 14
The object ‘ssn_unique’ is dependent on column ‘ssn’.
Msg 4922, Level 16, State 9, Line 14
ALTER TABLE DROP COLUMN ssn failed because one or more objects access this column.

So we need to drop the constraint in this case prior to dropping the column.

[cc lang=”sql”]
IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL
BEGIN
DROP TABLE ##meme
END

CREATE TABLE ##meme
(
first_name VARCHAR(50),
last_name VARCHAR(50),
ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE
)

ALTER TABLE ##meme
DROP CONSTRAINT ssn_unique

ALTER TABLE ##meme
DROP COLUMN first_name, ssn
[/cc]

I recently was able to use drop column to get around a massive update that took over an hour to perform. We obfuscate data as it is moved to another environment and rather than updating the data it was actually much quicker to drop the column and add it back along with a default value. Instead of taking an hour, the update now takes under one minute.