I'm doing a project that's adding some columns to a fairly badly designed table, and I noticed that it's got a lot of wasted space.
I see a lot of posts about adding columns on the end being relatively quick and cheap (for reasons I don't quite get – the follow-on links were broken), and I see a lot of posts about how expensive it is to grow columns, and how it essentially boils down to "make a new table and copy all the old data into it" (in fact ssms does that with Generate Script no matter what change you make).
I'm curious about a more niche concern I guess – altering columns to smaller data sizes and how to do that efficiently.
Specifically, this table has multiple datetime columns that really only want the date. In fact the sql is doing all the date arithmetic to strip time off of GETDATE(). I want to
ALTER TABLE Foo ALTER COLUMN BAR DATE NULL
But I don't want to incur all the expense of creating a temp table and re-writing the old data (if I can avoid it).
Seems like all the old data in place would be fine, just smaller.
And I'm hoping that freeing up that space in the block would make the new requirements I have to add less onerous (but I obviously don't get the deep internals of row allocation).
So what about going to smaller fixed-size types with an ALTER statement? Will that just be okay and not blow up the log?
Thanks
Best Answer
Changing from DATETIME to DATE changes the binary representation of the value, so the overhead will be similar to the one when increasing in size.
IIRC the only change that can be metadata only is one that doesn't change the binary representation, like changing from VARCHAR(100) to VARCHAR(50), and some may not even require scanning the data to make sure it fits, such as changing from VARCHAR(50) to VARCHAR(100).
Any change in data type that is not metadata only, will require physical update of the rows, and will cause a similar overhead as the entire row needs to be logged.