Very recently one of my client argued on the effect ALTER TABLE ADD or ALTER TABLE DROP column has on an existing table having millions of records.
AFAIK, the add column and drop column do not have a significant impact as the changes made are matadata changes.
Still, to prove the point , here is a small test with Add and DROP Column
create database text_altertable CREATE TABLE A1 ( ID int Identity, Name varchar(50), Account varchar(10), ) insert into A1 (name,Account) values ('test','200101') GO 100000 dbcc shrinkfile(2,0)
SET STATISTICS IO ON in Query Analyzer before issuing an ALTER TABLE command. If SQL Server is changing only metadata, you get no output back for STATISTICS IO. The ALTER TABLE command updates metadata only when SQL Server makes the following changes to a table:
dropping a column or constrain
disabling a constraint
disabling or enabling a trigger
In addition, SQL Server changes metadata only when you add a new column to a table without specifying a default value (the new column must allow NULL in that case) or when you modify a column definition to allow NULL values.
Let’s read the Index pages to see if there are any changes to the page
DBCC IND ('text_altertable','A1',-1)
Now examine the last page
DBCC TRACEON(3604) DBCC PAGE('text_altertable',1,673,3) WITH TABLERESULTS
As you can see, no changes happens to data pages.The same is with drop column with no data