After I changed the varchar limit on a column from 512 to max, in SQL Server 2008 R2, I noticed the file size of the table had more than doubled. I didn't expect it to increase this much, but since I was increasing it I thought it made sense. So I tried changing it to a more reasonable limit of 2500 only to find out that the size of my table again more than doubled in file size (now over 4x the original).
The only index is the Primary Key, and this is the script I ran:
ALTER TABLE dbo.Notes
ALTER COLUMN [note] VARCHAR(MAX) NULL
What is it that I'm not understanding?
For the same dataset, a varchar(max) should not take more space than a varchar(2500).
Perhaps SQL Server needed temporary space while altering the table? Try to rebuild your table (I'm assuming you primary key is clustered):
alter index NameOfPrimaryKey on dbo.Notes rebuild
And check the table data usage again after that.