What Alter Table ADD & DROP does to existing table with millions of records?

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

Leave a Reply

Your email address will not be published. Required fields are marked *